Xlookup multiple criteria

[mcis19]

Hi, I am doing a xlookup multiple criteria with boolean expression XLOOKUP(1,(B2=range 1)*(C2=range 2), range 3). However, the values in range 2 do not match all the time with C2 and I would like this part of the formula to look for a lower value in range 2 value provided the condition of range 1. Thanks a lot for the support!

[Bernie Deitrick]

Something like

=IFERROR(XLOOKUP(1,(B2=range 1)*(C2=range 2), range 3),XLOOKUP(1,(B2=range 1)*(range 2<C2), range 3))

[mcis19]

Hi,

Thanks for the answer but the formula did not work as expected, I think I did not explained myself. If I use the example of the first post, the result of the formula you suggested is in the column Prev.V,
while my expected result is in the column Exp.V. For example, the value y should start when the values C2 pass the threshold of 73008 of range 2.

Thanks a lot for your help! Really appreciated


[/CENTER]

2023-01-25 16_58_42-202301_Analysis_file_configurable - Excel.png

[CARACALLA]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[Bernie Deitrick]

In E2, for the layout shown below: Note the change in values in Range 2 to be one less than before....

=INDEX($B$3:$B$5,MATCH(D2,$A$2:$A$5,1))

Capture.JPG

[mcis19]

Hi everyone,

Thanks for your time and effort, and sorry if I did not post a clear Excel example. Please find it attached:

Tab 1: data
Tab 2: my actual calculations, the expected results I would like to obtain and the comments I made

Thank you very much.

[Haseeb Avarakkan]

Try this version.

=LET(a,SORT(FILTER(Table1[[Scale Quantity]:[Purchase Price]],Table1[Product]=A2)),IFERROR(LOOKUP(B2,a),INDEX(a,1,2)))

[mcis19]

Hi Haseeb,

this worked perfectly! Thank you so much, you saved my day!

Best Regards