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])
#18 – POWER QUERY
#19 – PIVOT TABLES
#20 – POWER PIVOT
Excel spreadsheet download
Download the sample file containing the solutions here.
You can learn similar tips and tricks in Excel at a comprehensive Training in London.