Reverse hlookup

I have a table with headers in the first row and unique values that are listed in the rows under that header.
Now in another table I have listed all of these unique values and I want to show the corresponding header next to it.

Seems like a job for hlookup, but it can only find the unique values in the first row, the header, and return a certain row, so naturally it returns #NV. Is there a way to do it the other way around? Find the value in rows 2-x and return the header in row 1?

Example file attached. In Column F I’m looking for the formula. In Column G I’ve written the output I need.

(I don’t care about the colors, they are just for easier identification)

Formula in F1:


May be =IF(COUNTIF($A$2:$C$6;E1)=1; INDEX($A$1:$C$1;SUMPRODUCT(($A$2:$C$6=E1)*COLUMN($A$1:$C$1)));"Is missing or not unique!")

Cool that works! Ingenious use of Sumproduct. Didn’t know such “smart uses” were possible with it. Gonna have to add that to my repertoire.


If the answer works for you, please consider to click the check mark (:heavy_check_mark:) next to the answer. That’s the way to indicate a correct answer. Thanks in advance …

oh sorry I didn’t realize there’s 2 possible interactions. I just upvoted before.