Search code examples
excelconditional-statementsxlookup

Excel - Find row with conditional statement in XLOOKUP

I'm trying to use XLOOKUP to find a value based on user inputs.

The table looks like this:

Type  Start  End   33    36   42    48
---------------------------------------
4002    1     7   1.17  1.34  1.5  1.84
4002    8     12  1.84  1.67  2.1  3.45

User selects type, number (can be between start and end), and 33-48

I can nest an XLOOKUP to specify the 3 criteria

=XLOOKUP(*type* & *number* , *typeRange* & *numberRange* ,XLOOKUP(*33-48* , *33-48Range* , *ResultRange* ))

And I can find if a value is between the columns

=IF(AND(*number*>=*Start*,*number*<=*End*),TRUE,FALSE)

Can I combine the two? The data is redundant for numbers 1-7, and I would like to keep the table small.

Solution

  • You sort-of can combine them. I have added a couple of extra rows to the table to see what would happen if you had different Type values as well as number values. The problem then is that if you used approximate match and put in a number like thirteen which is out of range, you might end up getting the next row of the table which would be incorrect. One way round it would be to use the options in Xlookup to search for next-smaller-item in the Start column and next-larger-item in the End column and see if the results match:

    =IF(XLOOKUP(I2&TEXT(J2,"00"),A2:A7&TEXT(B2:B7,"00"),XLOOKUP(K2,D1:G1,D2:G7),,-1)=XLOOKUP(I2&TEXT(J2,"00"),A2:A7&TEXT(C2:C7,"00"),
XLOOKUP(K2,D1:G1,D2:G7),,1),XLOOKUP(I2&TEXT(J2,"00"),A2:A7&TEXT(C2:C7,"00"),XLOOKUP(K2,D1:G1,D2:G7),,1),"Error")

    enter image description here

    If you have some checks in place which make it impossible for number to be out of range, then you can simplify the formula:

    =XLOOKUP(I2&TEXT(J2,"00"),A2:A7&TEXT(B2:B7,"00"),XLOOKUP(K2,D1:G1,D2:G7),,-1)

    or

    =XLOOKUP(I2&TEXT(J2,"00"),A2:A7&TEXT(C2:C7,"00"),XLOOKUP(K2,D1:G1,D2:G7),,1)