Tag Archives: DGET

20 Alternative Excel methods to VLOOKUP

20 Alternative Excel methods to VLOOKUP.

 

20 Alternative Excel Methods To VLOOKUP

 

Function VLOOKUP – Find and return related data from the other table.

Structured reference:
[@CURRENCY]  Lookup value
CurrencyTbl – Currency Table
CurrencyTbl[CURRENCY] – Column to look up against
CurrencyTbl[VALUE] – Column to return a value from

 

 

 

=VLOOKUP([@CURRENCY],CurrencyTbl,2,0)

#1 – LOOKUP 

=LOOKUP([@CURRENCY],CurrencyTbl)
=LOOKUP([@CURRENCY],CurrencyTbl[CURRENCY],CurrencyTbl[VALUE])

#2 – XLOOKUP (365)

=HLOOKUP([@CURRENCY],CurrencyTbl[CURRENCY],CurrencyTbl[VALUE])

#3 – INDEX + (X)MATCH

=INDEX(CurrencyTbl[VALUE],MATCH([@CURRENCY],CurrencyTbl[CURRENCY],0))
=INDEX(CurrencyTbl[VALUE],XMATCH([@CURRENCY],CurrencyTbl[CURRENCY]))

#4 – IF

=IF([@CURRENCY]=$V$5,$W$5,IF([@CURRENCY]=$V$4,$W$4,IF([@CURRENCY]=$V$3,$W$3,$W$6)))

#5 – IFS (2016+)

=IFS([@CURRENCY]=$V$6,$W$6,[@CURRENCY]=$V$5,$W$5,[@CURRENCY]=$V$4,$W$4,[@CURRENCY]=$V$3,$W$3)

#6 – SWITCH (2016+)

=SWITCH(TRUE,[@CURRENCY]=$V$6,$W$6,[@CURRENCY]=$V$5,$W$5,[@CURRENCY]=$V$4,$W$4,[@CURRENCY]=$V$3,$W$3)

#7 – CHOOSE + MATCH

=CHOOSE(MATCH([@CURRENCY],$V$3:$V$6,0),$W$3,$W$4,$W$5,$W$6)

#8 – INDIRECT + MATCH

=@INDIRECT("$W$"&(MATCH([@CURRENCY],CurrencyTbl[CURRENCY],0)+2),TRUE)

#9 – OFFSET

=OFFSET(CurrencyTbl[[#Headers],[VALUE]],MATCH([@CURRENCY],CurrencyTbl[CURRENCY],0),0)

#10 – REPT

=REPT($W$3,[@CURRENCY]=$V$3)&REPT($W$4,[@CURRENCY]=$V$4)&REPT($W$5,[@CURRENCY]=$V$5)

#11 – Boolean Logic

=([@CURRENCY]=$V$4)*$W$4+([@CURRENCY]=$V$3)*$W$3+([@CURRENCY]=$V$5)*$W$5

#12 – SUMPRODUCT

=SUMPRODUCT(--([@CURRENCY]=CurrencyTbl[CURRENCY]),(CurrencyTbl[VALUE]))

#13 – SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S)

=SUMIF(CurrencyTbl[CURRENCY],[@CURRENCY],CurrencyTbl[VALUE])

#14 – FILTER (365)

=FILTER(CurrencyTbl[VALUE],CurrencyTbl[CURRENCY]=[@CURRENCY])

#15 – DGET 

=DGET(CurrencyTbl[#All],CurrencyTbl[[#Headers],[VALUE]],Criteria)

#16 – Advanced Filter

#17 – MyLookup

=MyLookup([@CURRENCY],CurrencyTbl[CURRENCY],CurrencyTbl[VALUE])

#18POWER QUERY

#19PIVOT TABLES 

#20  – POWER PIVOT 

Excel spreadsheet download

Download the sample file containing the solutions here.

Download Free

You can learn similar tips and tricks in Excel at a comprehensive Training in London.