Tag Archives: HLOOKUP

Excel

20 Alternative Excel methods to VLOOKUP

Leave a comment

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])

#18 – POWER QUERY

#19 – PIVOT 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.

 

education, Excel

XLOOKUP vs VLOOKUP

Leave a comment

XLOOKUP vs VLOOKUP

xlookup vs vlookup

Forget about the VLOOKUP function. The new XLOOKUP function has arrived, being a genuine alternative for VLOOKUP, HLOOKUP, INDEX and MATCH. If you know how to use the XLOOKUP function, you will never bother to use VLOOKUP anymore. The new feature, which has been recently introduced, has more options and is characterised by its flexibility, efficiency and user-friendliness.

XLOOKUP has six arguments:

=XLOOKUP(Lookup_Value,Lookup_Array,Return_Array,If_not_Found,Match_Mode,Search_Mode)​​

1. Lookup_Value
2. Lookup_Array
3. Return_Array
4. [If_not_Found] – #N/A
5. [Match_Mode] – default FALSE – Exact match
6. [Search_Mode] – default TRUE – First to last

Benefits of XLOOKUP over VLOOKUP:
✅ the searched result in the column may be placed to the left of the column with the searched value;

✅ change of the structure of the table with data by inserting or removing columns will not affect the result;

✅ we select only relevant ranges and not necessarily the entire table;

✅ it can return a range, not just a value;

✅ it can return a value, if not found;

✅ the exact search is set to 0 by default in argument 4, the most commonly used option;

✅ it can return the nearest higher value if we declare (1) in the 4th argument;

✅ should we look for the closest value in the 4th argument we do not need to have any data sorted;

✅ wildcard support only if you declare (2) in the 4th argument;

✅ it can search for the last value (-1) in argument 5;

✅ it carries out binary search;

✅ it performs searches not only vertically, but also horizontally if we mark the ranges accordingly;

✅ faster functionality.

You can learn similar issues and tricks at my comprehensive Excel Training in London.