I have an Excel matrix/chart with dates across the top row, and certain cells with a specific value underneath the relevant dates. I'd like to generate a separate column which shows the dates for which that specific value occurs.

To illustrate this:

01-Apr   02-Apr   03-Apr        Date where 'x' occurs
           x                    02-Apr
  x                             01-Apr
                    x           03-Apr

...So I'm looking for a formula to generate the far right column, that will look for 'x' across each respective row and return the corresponding value from the top row (the date).

I have tried a combination of IF, HLOOKUP and MATCH functions but I cannot get the result I'm looking for. Any help would be much appreciated!

I use Excel 2016.

I have an Excel matrix/chart with dates across the top row, and certain cells with a specific value underneath the relevant dates. I'd like to generate a separate column which shows the dates for which that specific value occurs.

To illustrate this:

01-Apr   02-Apr   03-Apr        Date where 'x' occurs
           x                    02-Apr
  x                             01-Apr
                    x           03-Apr

...So I'm looking for a formula to generate the far right column, that will look for 'x' across each respective row and return the corresponding value from the top row (the date).

I have tried a combination of IF, HLOOKUP and MATCH functions but I cannot get the result I'm looking for. Any help would be much appreciated!

I use Excel 2016.

• excel
• excel-formula

1 Answer 1

So you can actually use an index & match to accomplish what you're trying to do. Hlookup, similar to vlookup can't look 'backwards' (or upwards).

First, the index should be the header row you want to check, lock this so it doesn't carry down as you drag the formula. Then for the "row" input, just put 1, since we're only searching that array. Then lastly, we use the match to search each row for the x and return the column number.

=INDEX($A$1:$C$1,1, MATCH("x",A2:C2,0))