question

MA-5779 avatar image
0 Votes"
MA-5779 asked MA-5779 commented

lookup value based on multiple criteria (including date ranges)

Hi there : I am trying lookup value based on multiple criteria (including date ranges). I am exploring different options such as Xlookup and Index/Match. When I use the Xlookup I get the correct result but when I utilize Index/Match functions I am getting #N/A values. I am not sure why the error is occurring. Any help to troubleshoot the Index/Match functions in this scenario would be greatly appreciated. Please see below the link for the functions that I tried

https://1drv.ms/x/s!Amc8fiGpDxeki3i1ixWIy2SWdmEE?e=dtm6gj
Many Thanks

92431-image.png


92382-image.png


office-excel-itpro
image.png (5.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered MA-5779 commented

@MA-5779

The greater than sign and less than sign are used incorrectly.

Please try this formula =IFERROR(INDEX($D$3:$D$13,MATCH(1,($B$3:$B$13<=$G3)*($C$3:$C$13>=$G3)*($A$3:$A$13=$F3),0)),"Not Found").

92504-capture37.png


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


capture37.png (16.2 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Emily: Thank you for your assistance. I used the > sign right way when I utilized the XLookup function but didn't do the same when I used the Index/Match function. Very foolish of me. Once I saw your solution, then I realized what is wrong with the syntax.
Have a good day.

=INDEX($D$3:$D$13,MATCH(1,($G3>=$B$3:$B$13)($G3<=$C$3:$C$13)($A$3:$A$13=$F3),0))

0 Votes 0 ·

Hey @MA-5779/Awal. Purely for the record:

 =IFNA(LOOKUP(1, 1/((A$3:A$13=F3)*(B$3:B$13<=G3)*(C$3:C$13>=G3)), D$3:D$13),"Not found")
1 Vote 1 ·

Hi Lz: Good to see you in the Excel forum and thanks for presenting an alternative solution using Lookup. I like using Lookup function in excel because it is flexible and similar to Lookupvalue dax function. May be one day PQ team would create something similar as M function.

Have a good one.

Cheers

0 Votes 0 ·