Reverse VLOOKUP

Question

Hi,I would like to&nbsp;find a data which is in the left side column of the lookup data.EXAMPLECol A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Col B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Col CXYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.3%ABC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15.2%EFG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 105&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.3%I would like to pick up the data in col A for the maximum value of Col C.&nbsp;Here in this example it is "ABC" as 15.2% is the maximum value.Col C value keeps changing on every update.&nbsp;TIAGNAGA&nbsp;

tauqeeracma · Answer

Hi&nbsp;gnaga&nbsp;&nbsp;Please try below formula:&nbsp;=INDEX(A:A,MATCH(MAX(C:C),C:C))&nbsp;Sample file is also attached for your reference. Hope it will work.&nbsp;ThanksTauqeer&nbsp;&nbsp;

haytham amairah · Answer

gnaga&nbsp;Hi,&nbsp;For now, the known formula for this case is INDEX &amp; MATCH combination as follows:=INDEX($A$1:$A$3,MATCH(MAX($C$1:$C$3),$C$1:$C$3,0))&nbsp;But soon there will be a new function in Excel called https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 which will be better and more convenient for this situation.&nbsp;Regards

twifoo · Answer

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)

Forum Discussion

Reverse VLOOKUP

3 Replies

Resources