VLOOKUP

·         The Excel VLOOKUP function is used to look for specified data in the left-most or the first column of a table of data.

·         Once found it will return a result, on the same row, a specified number of columns from the first column.

Syntax:  =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

E.G.   =VLOOKUP("John",LookupTable,4,False)

·         Note as the optional range_lookup/match-type Argument, False is specified.

·         This tells Excel to find an exact match typically required when looking for a text match.

·         If the range_lookup is omitted, or True, unwanted results may occur when searching for text in a column of data that is unsorted.

·         When True is specified, or the range_lookup argument is omitted, your data in the lookup column, i.e., column #1, should be sorted in ascending order.

The use of True, (or range_lookup Argument is omitted), is often applied when the 1st column of the table of data contains numeric data.

Named:  Lookup_Table = $B$12:$D$20

 

B

C

D

11

Amount

Name

Rank

12

$356.95

Bob

6

13

$225.63

Lee

7

14

$35.66

Harry

9

15

$648.00

David

2

16

$589.32

Hank

4

17

$600.25

Susan

3

18

$468.59

Heather

5

19

$1001.56

Kat

1

20

$109.95

Allana

8

E.G.   =VLOOKUP(468.59,Lookup_Table,2)

·         In the example above, we may expect a result of "Heather" but in fact "Harry" results because the range_lookup argument is omitted and our data is not sorted according to the Amount.

·         This is because VLOOKUP looks in Column B, starting from B11, and as soon as it finds the amount $648.00 it drops back to the cell above because $648.00 is greater than the lookup_value of 468.59.

·         If we sorted our data by Amount we would get the desired result.

 

We could also get the desired result by using: 

=VLOOKUP(468.59,Lookup_Table,2,False)

·         The use of False as the range_lookup Argument forces VLOOKUP to search all values and only stop when it finds an exact match.

·         If an exact match does not exist the #N/A! error is returned.

The method of sorting is best as a VLOOKUP that searches in a sorted range is MUCH faster and the effect can be significant especially if the table is large and/or there are many VLOOKUP functions.

How to stop the #N/A! error when VLOOKUP cannot find the data.

There are a few ways this can be done, the most popular way using ISNA is also the least efficient.

E.G.  =IF(ISNA(VLOOKUP(368.59,$A$1:$C$1000,2,False)),"",VLOOKUP(368.59,$A$1:$C$1000,2,False)

The use of the ISNA function in this way will force Excel to perform the VLOOKUP twice if the value does exist, which is most often the case.

·         This can become a problem by slowing down Excel's recalculation time.

 

A better option, though only slightly, is:

=IF(COUNTIF(Look_Table,468.59),VLOOKUP(468.59, Look_Table,2),"")

 

Note:  VLOOKUP is quite useful for numerical values contained in a range.  But its use with text in a table is limited, For example surnames such as Smith, Smithson, Smithy, Smithson-Jacobs would create problems.  If you entered a surname incorrectly, VLOOKUP will step back to the closest possible match. 

If you want to reference a table that uses text, it's advisable to use VLOOKUPs optional fourth argument called match-type/range_lookup.  This argument forces VLOOKUP to return #N/A if an exact match cannot be found in the first column of your table. This type of VLOOKUP is perfect to glean information from an address list.

*Note:  Examples below do not use named ranges but you should.

Suppose we wanted to find out the phone number of Smithson-Jacob.  We would use =VLOOKUP(B15,A2:E11,4,FALSE).  Telling Excel to lookup Smithson-Jacobs in the table range and return the value on the same row in the fourth column.  By using the optional fourth argument, FALSE, Excel is forced to return either an exact match (as it has done) or #N/A if it can't find an exact match.

In case we want to find the Party affiliation from within the Table we could use =VLOOKUP(A15,A2:E11,5,FALSE), which tells Excel to return the value in the fifth column on the same row.

Another way that we could write the same VLOOKUP is by referring to the Family name within the table range like this =VLOOKUP(A6,A2:E11,5,FALSE)

Left-Lookup

Excel is very rich in Lookup formulas, with perhaps the VLOOKUP being the most popular. However, the draw-back with all Excel's Lookup formulas is that they will only look in the left most column and return the result from the corresponding cell to the right. There are times when users need to lookup data in any column of a table and return the corresponding cell to the left. To do so, we can use the INDEX & MATCH Formula/Functions 

INDEX & MATCH

The INDEX Formula/Function has 2 versions available. We will only be using the first version here;

1) INDEX Formula/Function. Returns the value of a specified cell or array of cells within array.

Syntax
INDEX(array,row_num,column_num) 

2) INDEX Formula/Function. Returns a reference to specified cells within reference.

Syntax
INDEX(reference,row_num,column_num,area_num)

The MATCH  Formula/Function Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.


Syntax
MATCH(lookup_value,lookup_array,match_type)

See Excels help for full details on these 2 Formula/Functions.

Left Lookup

To do a left lookup we can use the INDEX Function/Formula with the MATCH Function/Formula nested in the Row_num Argument of the INDEX Function/Formula. Let's say our table of data resides in a table named DataTable and this named range refers to: $A$1:$D$9 See Image below;




As you can see, the first example uses the formula: =INDEX(DataTable,MATCH("RKP4",ID,0),1) and makes use of the Named ranges. The second does exactly the same, but does not use the Named ranges, i.e. =INDEX($A$1:$D$9,MATCH("RKP4",$C$1:$C$9,0),1)

In Laymen's terms, the formula is telling Excel to use the range $A$1:$D$9 as the array Argument, row 4 for the row_num Argument (returned via the MATCHFormula/Function) and Column 1 of the array $A$1:$D$9. Which returns the data in cell A5 (Mary K).

Returning the Column Number and Row Number

We can either take this a step further and ensure the column_num argument supplied is always correct by nesting another MATCH Formula/Function into thecolumn_num argument. The formula for this would be;

=INDEX(DataTable,MATCH("RKP4",ID,0),MATCH("Name",Headings,0))

OR, with no Named Ranges

=INDEX($A$1:$D$9,MATCH("RKP4",$C$1:$C$9,0),MATCH("Names",$A$1:$D$1,0))

With both the above examples, we can assure that moving the Names Column will not cause our formula to return an incorrect result.


Find the Nth Occurrence of Specified Value

As you may already know, we can use VLOOKUP, or INDEX/MATCH to locate the first occurrence of a specified value in a list, or table of data. However, Excel has no ready made formula that allows us to locate say the second, or third occurrence etc of a specified value. To do this would normally the use of a Custom Excel VBA Function, or a horribly inefficient Array formula. Below you will find 2 ways to locate the nth occurrence of a specified value and return the value on the same row but in another column. One is straight forward and requires no VBA, while the second does make use of Excel VBA. Neither need array formulas!

Find the Nth Occurrence of Specified Value.

I will use the table of data as shown below for the example, sorted only for easier reading. Note the formula that resides in the selected cell A2 (see Formula bar) is: =B2&" "&COUNTIF($B$2:$B2,B2) and is used to obtain a count of each occurrence. It is vital to note the absolution of $B$2 and the relative row reference of $B2. This is vital so when you copy the formula down, it will extend the COUNTIF range accordingly.



Note also that this extra column can be hidden from view to make for less clutter. If you have not already guessed, we now simply use a standard VLOOKUP, or INDEX/MATCH to obtain the occurrence we want, for example;

=VLOOKUP("Bill 3",$A$1:$C$22,3,FALSE)

Would yield a return value of "Bill # 3"