Lookup value from range when name matches AND date falls within date range

[eagerexceler]

Hello!

I have two sheets within a spreadsheet: Report and Rates. Report contains an account listing with a concatenated name in column S and a start date in column H. Within Rates I have triplicate rows containing the same concatenated name for each time frame in column F. Column G of the sheet contains the start date for the period - 9/1/2021, 9/1/2022, 9/1/2023 while column H contains the end date for each respective period -8/31/2022, 8/31/2023, 8/31/2024. Lastly, in the same sheet, column J has a corresponding value for each row. What I need is to return the value from column J in Rates when the common name in from S of Report matches that of Column F in Rates AND the admit date from column H of Report falls within the date range listed in columns G and H in CHIRP Rates. I want the value to be returned into column T of my Report tab.

I've attached screenshots of the two sheets. Sorry, I can't upload XL2BB since I'm at work.

Thanks for any help!

 

[eagerexceler]

The latest formula I've been using is INDEX('Rates'!J2:J43,MATCH(1,(('Rates'!F2:F43='Report'!S3)*('Rates'!G2:G43>='Report'!H3)*('Rates'!H2:H43<='Report'!H3)),0)) in array form, but I'm only returning #N/A, which is incorrect. Do I need to simplify my 'Rates' table? Is that throwing a wrench in my Matches?

 

[Alex Blakenburg]

Give this a try:

=LOOKUP(2,1/((Rates!F2:F43=S3)*(Rates!G2:G43<=H3)*(Rates!H2:H43>=H3)),Rates!J2:J43)

 

[eagerexceler]

Hi Alex, this is perfect, thank you!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.