Excel Tips

LOOKUP FORMULATION

 

Lookup Statements are another common formulation used in MS Excel.  The formulas can be very powerful and are relatively simple to construct.

There are two types of Lookup formula, VLOOKUP & HLOOKUP.  (LOOKUP formulation is not necessary and only contained within Excel for historical reasons.

=VLOOKUP(Lookup Value,Table Array,Column Index Number, [Range Lookup])

=HLOOKUP(Lookup Value,Table Array,Column Index Number, [Range Lookup])

 

VLOOKUP, looks in a range Vertically, whereas HLOOKUP looks in a range Horizontally.  The basic principles are the same however.

Lookup Value - This is the value you are looking for in your range

Table Array - This is the location of the Table that contains all your data

Column Index - This is the number of columns you want the formula to go across to return the corresponding value in the table.

 

IMPORTANT - Excel treats the first column in your table as column 1, thus, as in the example below where you want to return the value in the second column you would make sure the Column Index is 2.  (not 1)

 

[Range Lookup] - This should either be True or False.  If omitted altogether, Excel treats this criteria as True.

 

False - Means that Excel must find an exact match

True - Means that if Excel cannot find an exact match, then it will return the closest.

 

It is recommended that you always use FALSE unless the Lookups are Numerical.

 

VLOOKUP Example

 

Imagine you have the table in Cells B9 to C13:

 

You want a cell to look up a particular

stock type and display the quantity

next to it.  In this instance we would

use the VLOOKUP formula as the

data is arranged VERTICALLY, i.e. in

columns.

 

Thus we can use the formula:

 

=VLOOKUP(“Oranges”,$B$9:$C$13,2,false)

 

We can also use:

 

=VLOOKUP(A1,$B$9:$C$13,2,false)

 

Where we have a stock name typed in Cell A1.  (Oranges)

 

The formula simply ‘looks up’ the word “Oranges” in the table.  When it finds the match in the first column it simply goes across (to Column 2 within the table) and returns the value 75.

 

 

HLOOKUP Example

 

Imagine you have the table in Cells B9 to F10:

 

 

 

 

 

 

 

You want a cell to look up a particular

stock type and display the quantity

next to it.  In this instance we would

use the HLOOKUP formula as the

data is arranged HORIZONTALLY, i.e. in

rows.

 

Thus we can use the formula:

 

=HLOOKUP(“Oranges”,$B$9:$F$10,2,false)

 

We can also use:

 

=HLOOKUP(A1,$B$9:$F$10,2,false)

 

Where we have a stock name typed in Cell A1.  (Oranges)

 

The formula simply ‘looks up’ the word “Oranges” in the table.  When it finds the match in the first row it simply goes down (to Row 2 within the table) and returns the value 75.

 

FORMULA NESTING

 

Within segments of formulas you can usually nest other formulas.  Have a look at the IF Statements for help on nesting.

 

=IF(ISERROR(C32*((HLOOKUP($N$5,History,20,FALSE)*C36)/((HLOOKUP($N$5,History,20,FALSE)*$C$39)+(HLOOKUP($N$5,History,21,FALSE)*$C$44)+(HLOOKUP($N$5,History,22,FALSE)*$C$49)))),0,C32*((HLOOKUP($N$5,History,20,FALSE)*C36)/((HLOOKUP($N$5,History,20,FALSE)*$C$39)+(HLOOKUP($N$5,History,21,FALSE)*$C$44)+(HLOOKUP($N$5,History,22,FALSE)*$C$49))))

 

 

Got an issue with a Formula or an Excel query?  Email us and we can help!  excel@southcoastaccountants.com

We will make a charge, minimum of £5, but we would let you know how much it would cost before starting any work.  We can usually turn around in less than 1 working day.