CODEWITHSUNDEEP
excel-formula
Eric
Eric

Reputation: 1

Excel - Return Table Header with Multiple Criteria

I have a data table, where I need to be able to search multiple criteria and return the header value.

I have table of SKUs and Dates, and the inventory at each date. See example below. I want to be able to select a SKU Number, and a Date, and return the first instance where the date is greater than the date provided, and the inventory level is greater than zero.

See example below. I would want to see SKU 2, with input date of 4/4/2020. The first date with SKU 2 with a inventory greater than 0 after 4/4/2020 would be 4/6/2020. That is the example output I am looking for.

Any help would be greatly appreciated!

enter image description here

Upvotes: 0

Views: 403

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Or, J8 formula :

=AGGREGATE(15,6,D4:G4/(INDEX(D5:G9,MATCH(J4,C5:C9,0),0)>0+RIGHT(J6))/(D4:G4>=J5),1)

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Use:

=INDEX(4:4,AGGREGATE(15,7,COLUMN(D4:G4)/((D4:G4>=J6)*(INDEX(D:G,MATCH(J5,C:C,0),0)>0)),1))

enter image description here

Upvotes: 0

Related Questions