Category Archives: education

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.

 

Excel Named Range

Excel Named Range

Defining names in Excel involves giving a range of cells a unique name, which means that you can easily refer to a specific defined range, e.g. while creating a function. This is especially applicable in the following functions:
as the first argument
MATCH as the second argument
VLOOKUP and HLOOKUP as the second argument.
Formulas containing names instead of ranges are more understandable and easier to use, provided that the names we give will be legible. These names are also used in VBA programming.
In the Formulas card you will find the Defined names section:

How to define name for the range?

• Select the relevant range.
• In the Name Box, enter the unique name of the selected range. Do not use any illegal special characters, e.g. spaces, $, @, #,! or arithmetic operators (+, -, /, *, ^), comparison (<> =) and the text operator (&).
• Accept by pressing the Enter key.

Instead of the Name box you can click Formulas card then Define Name, set up: the name, scope, comment and refers to. In addition, the defined name cannot only refer to a cell or range of cells, but it also contains a formula or a specific value or text.

Create multiple names from the selection

• Select a range of data which contains multiple columns or rows with the labels at the beginning or the end.
• Click Formulas card then Create form Selection or shortcuts[Ctr] + [Shift] + [F3].
• Select options based on what you want to name.

Name Manager

• Click Formulas card then Name Manager [Ctrl] + [F3].
• Create another new name, edit existing name, delete it, filter it and sort it

Insert the defined name into the formula

Simple click [F3] to obtain a list of created names and select the relevant one or start typing existing name and select the relevant one.

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

IFS vs nested IF

IFS vs. nested IF

Excel IF Statement

Syntax of Excel IF Statement:​

= IF ( Logical Test , Value if True , Value if False )​​

Logical test Examples:

• Equal to: A1=A2.
• Higher than: A1>A2.
• Higher or equal than: A1>=A2.
• Less than: A1<A2.
• Less or equal than: A1<=A2.
• Not equal to: A1<>A2.

If we do not declare a third argument, because it is not mandatory, it will return FALSE by default. If we want to return the text as a result in the second or third argument, it must be enclosed in quotation marks.

=IF(A1>5,"Pass","Fail")

If cell A1 value is higher then five, formula return Pass otherwise Fail.

In the case when we want to apply one logical condition, creating an IF function is simple and obvious. However, if we want to introduce further conditions, everything gets slightly more complicating. In this case, the function is nested, the next function (function in function or multiple) as an argument of the function.

Nesting of the IF function

I think each of us, at least once, has found it difficult to master the IF function when it is nested. That is, when the IF function was an argument of the IF function at least once. In addition, the nested function is a burden on the work of the system during conversion, of course, when we often use this function in the worksheet.
I will now give you a simple example of an IF function nested once. In place of the third argument, a new IF function containing another condition is introduced.

= IF ( Logical Test , Value if True , IF ( Next Logical Test , Value if True , Value if False ))
=IF(A1>5,"Excellent",IF(A1>3,"Good","Poor"))

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return Good,  otherwise Poor.

IFS function alternative

In 2016, an alternative to the IF function appeared in Excel, i.e. the IFS function, which gives the possibility to introduce more logical conditions than one, which simplifies the creation of the formula and significantly speeds up its work.

= IFS ( First logical Test , Value if True , [ Next Logical Test , Value if True ] , ...)
=IFS(A1>5,"Excellent",A1>3,"Good")

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return three,  otherwise return error #NA.

It can happen that more than once the condition is met in the arguments of the IFS function. In this case, the one that is recorded first is decisive.

=IFS(A1>3,"Good",A1>5,"Excellent")

In this case, if the content of cell A1 is higher than three, the function will always return Good (never return Excellent). Therefore, the order in which the conditions are saved is important. If none of the conditions are met, the formula will return error #NA. 

IFS vs nested IF

Benefits of IFS over nested IF:

• The IFS function allows you to easily enter further logical conditions without nesting.
• Due to the lack of nesting, the performance of IFS is much more efficient
• Allows you to apply twice as many, or 127 conditions, while the nested formula 64 conditions only.
• If none of the conditions are met, IFS will return error #NA. In this case, it is worth using IFEROOR. In this case, in the second argument we give what should be if an error occurs, if we need it.

= IFERROR ( IFS ( Logical Test , Value if True , Next Logical Test , Value if True ) , Value if False )
=IFERROR(IFS(A1>5,"Excellent",A1>3,"Good"),"Poor")

If the value in cell A1 is higher than five, the formula return Excellent, if not, but higher than three return Good, otherwise it return Poor.

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