Index Match with multiple criteria and date criteria

**snolem75** · 03-26-2018, 04:55 PM

Hello and thank you in advance for taking the time to look at my question

...

I have uploaded a sample file for what I am looking for.

In table 1, we have a column for a buyer name and another column for the date. Then we have a column for each item for sale.

In the portion where there is a column for each item, I need to report (from Lookup Table tab) the price we are selling the item at to that Buyer for that date.

Prices change, so there is an effective date column in the Lookup Table tab and the match must be the buying date must be greater than or equal to the date in the lookup table.

I'm assuming the lookup table will need to be sorted by descending date?

Please let me know how to solve this.

Thank you in advance again, very much for your help excel gurus!.

Cheers!

**benishiryo** · 03-26-2018, 09:04 PM

hi there. if you can sort your table by Buyer, Item and Effective Date (ascending), then:
=LOOKUP(2,1/(('Lookup Table'!$A$2:$A$15=$A3)*('Lookup Table'!$D$2:$D$15<=$B3)*('Lookup Table'!$B$2:$B$15=C$2)),'Lookup Table'!$C$2:$C$15)

if you can't, maybe:

Formula:

Please Login or Register  to view this content.

do also consider to input the desired results next time so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

**kvsrinivasamurthy** · 03-27-2018, 02:10 AM

In C3 the drag acoss

Please Login or Register  to view this content.

**Czeslaw** · 03-27-2018, 08:56 AM

The proposed formula

Formula:

Please Login or Register  to view this content.

**snolem75** · 03-28-2018, 03:51 PM

You guys are all incredible geniuses!

Thank you very much for your help and your time =)

Index Match with multiple criteria and date criteria

LinkBack

Thread Tools

Rate This Thread

Display

Index Match with multiple criteria and date criteria

Re: Index Match with multiple criteria and date criteria

Re: Index Match with multiple criteria and date criteria

Re: Index Match with multiple criteria and date criteria

Re: Index Match with multiple criteria and date criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index Match multiple criteria based on last date

[SOLVED] Index match with multiple criteria, with one criteria being greater than a certain date

[SOLVED] Index Match with multiple criteria including date range

[SOLVED] Sum Index Match Dates Between Multiple Date Criteria

INDEX / MATCH FUNCTIONS - Multiple Criteria Between Date Ranges

Index Match with Multiple Criteria One to Nearest Date

[SOLVED] Index Match equal to or less than date with multiple criteria

Bookmarks

Bookmarks

Posting Permissions