Look up between less than or equal to HELP

Copper Contributor

MrShady_1-1719794372427.png

Hey there people,

This may be a very simple array or LOOKUP formula but my brain doesn't work like it used to.

If you look at the table above, I am setting up a spreadsheet to look up a figure based of width and height fitting within certain parameters. (This is to do with blinds by the way). If I put in the following: 

WIDTH:  2.2

HEIGHT: 2.6

I would need the formular to not only work out where the number sits (I.e. width is less than 2.5 but more than 2.0 but also that the height is less than 3.0 and more than 2.5) and then LOOKUP to give me a result of $1,095.00 

I'm wondering if the format of the table I have made needs to be reworked so it will be simpler to do.

Any helpful advice is greatly appreciated.

Thanks,

Mr Shady

 

2 Replies

@MrShady You will need nested XLOOKUP() with Exact match or next larger item parameter. Try

 

 

=XLOOKUP(D8,B2:B4,XLOOKUP(D7,C1:L1,C2:L4,0,1),0,1)

 

 

Nested FILTER() function will also work. See the attached file.

 

=TAKE(FILTER(FILTER(C2:L4,C1:L1>=D7),B2:B4>=D8),1,1)

 

Harun24HR_0-1719802300361.png

 

 

NAILED IT!! Thank you so so much, you're a legend!! :)