Look-up with multiple conditions (including a NEAREST value look-up)

[BWilson]

Hi,
There's lots of info about looking up a value with multiple criteria (vlookup with helper function, xlooup, index-match, etc). I can get this to for exact-match look ups, but I can't figure it out when one variable requires a nearest value look-up.

In the example below, I have my data on the left (Object and Size), and I need to pick the object category based on the table on the right (ObjCat, Object, MinSize). I can get the look-up with the Object just fine, but how can I write a formula that ALSO uses a nearest-value look-up with the Size? In this case, nearest value being the next smallest. I can sort the tables by size if necessary.

Object	Size	LOOK_UP RESULT		ObjCat	Object	MinSize
A	3	A_Sml
A	10	A_Med		A_Sml	A	1
B	2	B_Sml		A_Med	A	10
B	4	B_Sml		A_Big	A	20
C	15	C_Med		B_Sml	B	1
C	35	C_Big		B_Med	B	5
				B_Big	B	10
				C_Sml	C	1
				C_Med	C	10
				C_Big	C	30

Thanks!

-B

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[BWilson]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Office 365, Windows. Updated my account details.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Object	Size	LOOK_UP RESULT		ObjCat	Object	MinSize
2	A	3	A_Sml
3	A	10	A_Med		A_Sml	A	1
4	B	2	B_Sml		A_Med	A	10
5	B	4	B_Sml		A_Big	A	20
6	C	15	C_Med		B_Sml	B	1
7	C	35	C_Big		B_Med	B	5
8					B_Big	B	10
9					C_Sml	C	1
10					C_Med	C	10
11					C_Big	C	30
Master

Cell Formulas
Range	Formula
C2:C7	C2	=TAKE(FILTER($E$3:$E$11,($F$3:$F$11=A2)*($G$3:$G$11<=B2)),-1)

 

[BWilson]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Object	Size	LOOK_UP RESULT		ObjCat	Object	MinSize
2	A	3	A_Sml
3	A	10	A_Med		A_Sml	A	1
4	B	2	B_Sml		A_Med	A	10
5	B	4	B_Sml		A_Big	A	20
6	C	15	C_Med		B_Sml	B	1
7	C	35	C_Big		B_Med	B	5
8					B_Big	B	10
9					C_Sml	C	1
10					C_Med	C	10
11					C_Big	C	30
Master

Cell Formulas
Range	Formula
C2:C7	C2	=TAKE(FILTER($E$3:$E$11,($F$3:$F$11=A2)*($G$3:$G$11<=B2)),-1)

Logically your solution makes sense. Unfortunately, when I tried implementing it in my own spreadsheet and I'm getting a #VALUE! return. With a little troubleshooting, I find the first half of the filter (range = cell) works fine, but the (range <= cell) part is causing the problem. Did it work for you?
I've also found a workaround with my dataset, unrelated to my question. So I won't spend much more time on this forum thread, but will mark you solution as correct if the formula is valid for the sample dataset (where my spreadsheet could have a different problem.)

 

[Fluff]

Did it work for you?

Yes it did as can be seen in post#4