Google-sheets – Google Sheets Inverse (or Reverse) LOOKUP … is this possible

google sheetsvlookup

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?

Best Answer

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
Related Topic