We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

reverse hlookup

asked 2021-06-09 14:34:51 +0200

Stephan S. gravatar image

updated 2021-06-09 14:38:58 +0200

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. C:\fakepath\example.ods

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
3

answered 2021-06-09 15:13:33 +0200

PKG gravatar image

updated 2021-06-09 15:27:09 +0200

Formula in F1:
=INDEX($A$1:$C$1,SUMPRODUCT(($A$2:$C$6=E1)*(COLUMN(A$1:C$1))))

C:\fakepath\1623242247551862.ods

edit flag offensive delete link more

Comments

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!")

JohnSUN gravatar imageJohnSUN ( 2021-06-09 15:30:21 +0200 )edit

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.

Stephan S. gravatar imageStephan S. ( 2021-06-09 15:49:56 +0200 )edit

@Stephan S.

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

Opaque gravatar imageOpaque ( 2021-06-09 16:23:57 +0200 )edit

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

Stephan S. gravatar imageStephan S. ( 2021-06-09 17:21:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-06-09 14:34:51 +0200

Seen: 45 times

Last updated: Jun 09