Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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?
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
=tranposewith=match?