lookup value based on multiple criteria (including date ranges)

MA 101 Reputation points
2021-04-29T01:18:28.957+00:00

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

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,341 questions
No comments
{count} votes

Accepted answer
  1. Emily Hua-MSFT 21,686 Reputation points Microsoft Employee
    2021-04-29T07:40:06.81+00:00

    @MA

    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.


0 additional answers

Sort by: Most helpful