# 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)`

#17 – MyLookup

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

#18POWER QUERY

#19PIVOT TABLES

#20  – POWER PIVOT