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.
Thanks!
-B
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