CODEWITHSUNDEEP
excelsearchindexingfiltermatch
Noah Khounsombath
Noah Khounsombath

Reputation: 25

Search Excel for multiple values based on column header and row filter

I have a table of data that gives the daily price of gas based on Supplier ID (column B) and Gas type (column C). As you can see, the price values are under a date column headers. I'd like to to retrieve the values for a specific rows and column header. I've tried to use a INDEX and MATCH, and i've only managed to extract 1 row of value when i enter the supplier ID (gives me the 1st matching row). I also had trouble addind the critera of date in the function since it's column headers.

Want I'm trying to do : Based on a value selection of Supplier ID and date, extract the prices (column J to M) for each associated gas type (if there is). So for exemple the user would input in 2 cells the values 22.0256 (supplierID) and 12-jun(date), the result would give price values for the 3 associated gas type. A thing to keep in mind is the Ramp Price (Column J) is always the same as the most up-to-date price (in this case 17-jun)

Pardon my english as it is not my 1st language. See images to understand. The 2nd image is the result i'd like :) THANKS A LOT!

Sample of data

enter image description here

Upvotes: 0

Views: 45

Answers (1)

David Amato Mantegari
David Amato Mantegari

Reputation: 13

I think you can use a XLOOKUP and CHOOSECOLS:

=IFERROR(CHOOSECOLS(XLOOKUP([user input cell]; B:B;C:M;"";0;1);1;-4;-3;-2;-1);"Not found")

If you want to add a column with the date of interest you can add a MATCH on CHOOSECOLS to select the column with that date:

IFERROR(CHOOSECOLS(XLOOKUP([user input cell]; B:B;C:M;"";0;1);1;1+MATCH([date input cell];D2:I1);-4;-3;-2;-1);"Not found")

Upvotes: 0

Related Questions