1

As far as I can tell, all LOOKUP functions require you to know a specific search_key (like a column or row header) in order to search for and retrieve a relative value.

Inversely, is there a way to find what the search_key is if you only know the value?

So, for example, if you have a matrix of values, how would one search through the matrix of unique values and return the column header?

a|b|c|d
1|2|3|4
5|6|7|8

What letter column is the number 7 in, for example?

  • You can maybe try =tranpose with =match ? – pjmg May 15 at 7:33
1

Try:

=SUBSTITUTE(address(1,match(7,query(A1:D3,"select A, B, C, D where A=7 or B=7 or C=7 or D=7",0)),4,1), 1, "")

The logical progress is:

  • query - returns the values in the row of the two dimensional matrix where one of the values = "7"
  • match - returns the index number of the cell that equals "7"
  • address - returns an address for the cell. in this case, the row value is not important, so we use "1" for the sake of convenience.
  • substitute - this takes the cell address and substitutes the number "1" for blank. The result is just the column letter
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.