Reverse VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-919631%22%20slang%3D%22en-US%22%3EReverse%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919631%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%26nbsp%3Bfind%20a%20data%20which%20is%20in%20the%20left%20side%20column%20of%20the%20lookup%20data.%3C%2FP%3E%3CP%3EEXAMPLE%3C%2FP%3E%3CP%3ECol%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Col%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Col%20C%3C%2FP%3E%3CP%3EXYZ%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20123%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2012.3%25%3C%2FP%3E%3CP%3EABC%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B345%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B15.2%25%3C%2FP%3E%3CP%3EEFG%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20105%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2010.3%25%3C%2FP%3E%3CP%3EI%20would%20like%20to%20pick%20up%20the%20data%20in%20col%20A%20for%20the%20maximum%20value%20of%20Col%20C.%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20in%20this%20example%20it%20is%20%22ABC%22%20as%2015.2%25%20is%20the%20maximum%20value.%3C%2FP%3E%3CP%3ECol%20C%20value%20keeps%20changing%20on%20every%20update.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3CP%3EGNAGA%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-919631%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919660%22%20slang%3D%22en-US%22%3ERe%3A%20Reverse%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919660%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F429297%22%20target%3D%22_blank%22%3E%40gnaga%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20below%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(A%3AA%2CMATCH(MAX(C%3AC)%2CC%3AC))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%20Hope%20it%20will%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919670%22%20slang%3D%22en-US%22%3ERe%3A%20Reverse%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F429297%22%20target%3D%22_blank%22%3E%40gnaga%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20now%2C%20the%20known%20formula%20for%20this%20case%20is%20INDEX%20%26amp%3B%20MATCH%20combination%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(%24A%241%3A%24A%243%2CMATCH(MAX(%24C%241%3A%24C%243)%2C%24C%241%3A%24C%243%2C0))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20943px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F138567iBE69291CB5A77B00%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22INDEX%20%26amp%3B%20MATCH.png%22%20title%3D%22INDEX%20%26amp%3B%20MATCH.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EBut%20soon%20there%20will%20be%20a%20new%20function%20in%20Excel%20called%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fxlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EXLOOKUP%3C%2FA%3E%20which%20will%20be%20better%20and%20more%20convenient%20for%20this%20situation.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919698%22%20slang%3D%22en-US%22%3ERe%3A%20Reverse%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919698%22%20slang%3D%22en-US%22%3EAnother%20alternative%20is%20the%20powerful%20combination%20of%20LOOKUP-FREQUENCY%2C%20like%20this%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C1%2F(%3CBR%20%2F%3EFREQUENCY(0%2C1%2F(1%2BC%241%3AC%243)))%2C%3CBR%20%2F%3EA%241%3AA%243)%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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

 

3 Replies
Highlighted

Hi @gnaga 

 

Please try below formula:

 

=INDEX(A:A,MATCH(MAX(C:C),C:C))

 

Sample file is also attached for your reference. Hope it will work.

 

Thanks

Tauqeer

 

 

Highlighted

@gnaga

 

Hi,

 

For now, the known formula for this case is INDEX & MATCH combination as follows:

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

INDEX & MATCH.png

 

But soon there will be a new function in Excel called XLOOKUP which will be better and more convenient for this situation.

 

Regards

Highlighted
Another alternative is the powerful combination of LOOKUP-FREQUENCY, like this:
=LOOKUP(2,1/(
FREQUENCY(0,1/(1+C$1:C$3))),
A$1:A$3)