Vlookup in reverse???? Column heading needed.

[philobr]

Hi there,
I have 20 columns of numbers (which have been formated as text to display leading 0)

I have separate list of numbers in one column, formatted in the same way.

Is there a way to do a reverse lookup, ie look in this range and give me the column heading?????

 

[Jonmo1]

Try using Index and Match...

=INDEX(A1:Z1,MATCH(somevalue,A10:Z10,0))

That will find somevalue in Row 10, and return corresponding value from Row 1

Hope that helps.

 

[philobr]

This is what I added:
=INDEX($G$7:$AR$7,MATCH(A8,$G$8:$AR$57,0))

The header rows are in $G$7:$AR$7

I got an #N/A thrown back.

There is a value in column G that matches A8 in fact, its the first entry

 

[Jonmo1]

So the value in A8 could be anywhere within G8:AR57?

Will there be only 1 match for A8 in that range, or could there be multiple?
If there are multiple, which one do you want?

 

[philobr]

Yes, the value in A8 could be anywhere in the lookup_array.

There should only be one instance in the lookup_array, I dont mind getting an error if there are multiple values because thats a different issue.

 

[Jonmo1]

With no duplicates in the array, try

=INDEX(G7:AR7,SUMPRODUCT((G8:AR57=A8)*COLUMN(G8:AR57))-COLUMN(G8:AR57)+1)


If there are duplicates, this will return the one in the leftmost column
=INDEX(G7:AR7,MIN(IF(G8:AR57=A8,COLUMN(G8:AR57)))-COLUMN(G8:AR57)+1)

The 2nd one is an array formula that must be entered with CTRL + SHIFT + ENTER

 

[philobr]

That worked a treat, thanks a million.

 

[abccells]

Nice how to get all the Headings (2nd Row) matching value (0 in 4th row of the sheet) in a desired cell with comma seperation Jai