Reverse VLOOKUP....Is it possible??

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
614
Hi,

I would like to find a data which is in the left side column of the lookup data.

EXAMPLE

Col A Col B Col C

XYZ 123 12.3%

ABC 345 15.2%

EFG 105 10.3%

I would like to pick up the data in col A for the maximum value of Col C.

Here in this example it is "ABC" as 15.2% is the maximum value.

Col C value keeps changing on every update.



TIA

GNAGA
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

You could test following

=INDEX($A$1:$A$4,MATCH(MAX($C$2:$C$4),$C$1:$C$4,0))

Hope this will help
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
614
Thanks fro your help.

I am getting #REF error when use this in my real data table.

I am skipping the header row. Is this a problem?
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
You could try

=INDEX($A$2:$A$4,MATCH(MAX($C$2:$C$4),$C$2:$C$4,0))
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
614
Yes this was I tried by skipping the header row. Still I am getting the #REF error.

My data range is B4:N153

The column N having a number stored in % format. I would like to pick the data in Column B for the maximum % value in Column N.

within this range I hid few rows and columns - Is this making any problem?
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Can you post the formula you are using ...

Why are you pointing to " Reverse VLOOKUP " as the solution to your question ... ???
 
Last edited:

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
614
=INDEX($B$4:$N$153,MATCH(MAX($N$4:$N$153),$N$4:$N$153,0))

I posted it as Reverse Lookup, because my data (Col B) which is to be picked is in the left side of the reference data (Col N).
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
614
Sorry I corrected my Formula and working fine.
=INDEX($B$4:$B$153,MATCH(MAX($N$4:$N$153),$N$4:$N$153,0))
 

Forum statistics

Threads
1,084,962
Messages
5,380,841
Members
401,699
Latest member
ijazkhan01

Some videos you may like

This Week's Hot Topics

Top