Multiple Column lookup and return corresponding value in next column (nearest value)

Home / Uncategorized / Multiple Column lookup and return corresponding value in next column (nearest value)

Question:
Here’s what I am trying to accomplish, I have two sheets:
Reference Sheet: Click to see the image

Code Length Width Height

A 78 48 25
B 78 48 34
C 12 7.4 5
D 12 15 5
E 12 15 7.5
F 12 15 9
G 24 15 5
H 24 15 7
Solution Sheet:
Click to see solution example

Length Width Height Returning Code Match_L Match_W Match_H

10 6 8 C 12 7.4 5
The formula in Column "Returning Code" should look for the nearest value in the corresponding Reference Sheet i.e., Length <-> Length, Width <-> Width, Height <-> Height and return the matching "Code" from the corresponding row.

It would have been simpler if I want to match it when the values are equal but in my case, it will be looking for the nearest value (either greater or lower) in each of the corresponding columns and return the matching "Code" and the values in Match_L, Match_W, Match_H columns.

Any help or pointers are highly appreciated!


Answer:
Assuming that there is only a single place for entering the required length, width and height, and thus only one returned value at its maximum:
In your reference sheet add three more columns in E to G: length_dif, width_dif and height_dif.

The formula for these columns will be in cell E2: =ABS(B2-SolutionSheet!A$2) then expand this to G2 and draw it down till the end of your solution table.

Add another column in your reference sheet in H: dif_abs with the formula: =Sum(E2:G2)

Then to return your value add the following formula in your SolutionSheet in cell D2: =Index(ReferenceSheet!$A$2:$H$9;MATCH(Min(ReferenceSheet!$H$2:$H$9);ReferenceSheet!$H$2:$H$9);1)
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *