Given a value in an table of values in Excel, how to return the corresponding value from the first column/row

Question

I have the following data-set:

Given a number that occurs only once in the data range B2:E5, I would like to use a formula that returns the corresponding date value. The date values are given in the first column (A2:A5). I would also like to be able to return the corresponding hour value using a similar formula. Note, it is not necessary to return the hour and the date for a given value using the same formula.

Example: In the image of the data-set above – given the number 5, I would like to return the corresponding date. In this case, the formula would return the date value 03/01/2013. Similarly, I would also like to be able to return the "hour 3", given the same initial value.

Maybe the answer to this question is quite straight forward, but as of yet I have had no luck in figuring it out. Some things that I have tried, but to no avail, are the following: VLOOKUP/HLOOKUP, LOOKUP, a combination of INDEX+MATCH, and a combination of INDEX+MATCH+MATCH.

Any help is very much appreciated.

Answer 1

Given that Number to Find is unique, you can use this:

For the date in Column 1

=INDEX(Table1[Column1],MAX((NumToFind=Table1 )*ROW(Table1 ))-1)

For the hour in the Headers fields:

=INDEX(Table1[#Headers],1,MAX((NumToFind=Table1)*COLUMN(Table1)))

The above formulas are array formulas and must be confirmed by holding down CTRL + SHIFT ENTER

The -1 in the first formula is to compensate for the fact that the table starts in Row 1, with the data starting in Row 2, and the formula determines the absolute row number; if it starts other than in A1, different compensating values will be required.