Lookup in Tree style table in excel using index match

Company | Sector | Date
ABC       XYZ      Q1 2019
                   Q2 2019
DEF       KBP      Q1 2018
TGH       LHK      Q3 2020

Imagine "Company" is Cell A1 and "Q2 2019" is Cell C3. I would like to be able to search that value, so use the company name "ABC" as a reference to lookup. But I could only retrieve the value on the same row, this would have to be dynamic as the number of dates on the file change.

I know there's something I could do using OFFSET, ROW, MAX, ISBLANK, etc, but I'm a bit lost in terms of how to start.

Joel

Posted 2019-10-08T15:43:45.697

Reputation: 1

2If you have the Company and the Date, what are you trying to return? The Sector perhaps? – Jeeped – 2019-10-08T15:54:39.293

Perhaps an if formula can be used to fill in those blanks. Put the formula in a new column (say column d) and if the cell is blank take the answer from the cell above (in column d). Then maybe another helper column to combine company and date to do your index/match (as I don't think index/match can look for criterias in different cells) – gns100 – 2019-10-08T19:10:43.213

You can use Power Query to fill the blanks with the contents of the row above, making lookups simpler. – Ron Rosenfeld – 2019-10-08T19:54:42.687

Lookup in Tree style table in excel using index match

No answers