Excel Forum

Lookup to nearest matching value

baxbax

Member

Members

Power Query

Power Pivot

Forum Posts: 105

Member Since:
July 11, 2016

Offline

March 29, 2019 - 8:06 pm

Hi,

Is there a way to add the nearest matching value to a row similar to Excel VLOOKUP approximate match?

I have a data set that shows the weights and dimensions of products. In some instances the weight is missing but I have another table that shows an average weight based on the products diagonal size. I want to populate the missing weight fields with the average one using the diagonal size which is a field that exists in both tables. However the diagonal size values do not match exactly so I want to pick up the weight from the nearest diagonal size.

For example in the data set I have an item with no weight where the diagonal size is 22 inches. In the average weight table it shows that the average weight for a 20 inch product is 5kg and for a 30 inch product it is 10kg. I want to pick up the weight of the nearest matching item so in this instance it would be 5kg.

I have attached a file showing some example data.

Thanks

Bax

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 870

Member Since:
December 7, 2016

Offline

March 30, 2019 - 2:37 am

Hello,

I don't see any query in your file, so below is the Excel formula I assume you want.

=VLOOKUP(MROUND(I8,10),Table_Lookup,2,TRUE)

Click respective link to read more about MROUND and VLOOKUP.

Br,

Anders

baxbax

Member

Members

Power Query

Power Pivot

Forum Posts: 105

Member Since:
July 11, 2016

Offline

March 30, 2019 - 8:06 pm

Hi Anders,

File attached now has a query added. I want to somehow combine the two queries in the file to fill in the missing weights in the Query_data table.

Thanks

Bax

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1807

Member Since:
November 8, 2013

Offline

March 30, 2019 - 8:27 pm

Hi baxbax,

you can use the following function, that replicates the nearest match lookup:

(Parameter)=>
let
Source = Excel.CurrentWorkbook(){[Name="Table_Lookup"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Diagonal (in)", type number}, {"Weight (kg)", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Diagonal (in)", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [#"Diagonal (in)"] <= Parameter),
Group = #"Filtered Rows"{0}[#"Weight (kg)"]
in
Group

baxbax

Member

Members

Power Query

Power Pivot

Forum Posts: 105

Member Since:
July 11, 2016

Offline

April 1, 2019 - 6:08 pm

Hi Catalin,

Your solution works really well. Do you have any explanation as to how this works as I would like to understand it a bit more so I can use in future.

Thanks

Bax

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1807

Member Since:
November 8, 2013

Offline

April 1, 2019 - 9:42 pm

The function is selecting only the rows from the lookup table where the Diagonal is smaller than the parameter passed to this function:

Table.SelectRows(#"Sorted Rows", each [#"Diagonal (in)"] <= Parameter),

Because this table is already sorted descending, the very first row will contain our needed value: ({0} means first row, it's zero based)

Group = #"Filtered Rows"{0}[#"Weight (kg)"]

baxbax

Member

Members

Power Query

Power Pivot

Forum Posts: 105

Member Since:
July 11, 2016

Offline

April 2, 2019 - 7:13 pm

Hi Catalin,

Thanks for taking the time to add the explanation. It really helps.

Bax

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Andy Kirby, Jill Niemeier, Laxmi Praveen

Guest(s) 10

Currently Browsing this Page:
1 Guest(s)