Excel VLOOKUP

Excel VLOOKUP

Didn't find your answer?

Is there an easy way to understand how to use VLOOKUP in excel? It is part of a course and books all make it seem complicated. Is there a 'Fools ' guide to this subject in a more simple way than the Excel manual?
Michael Barron

Replies (2)

Please login or register to join the discussion.

avatar
By jfranco
25th Sep 2010 01:06

Vlookup fools guide

The best way to understand Excel Vlookup function is by comparing it to your index finger.

Yes your index finger...

Imagine looking up a phone number in a directory

You move your finger down until you get the name you are looking for, then you move your finger to the right to pick the phone number.

This video may help:

http://www.youtube.com/watch?v=leICQIRQDTc

Thanks (0)
avatar
By ACDWebb
25th Sep 2010 01:52

VLOOKUP up runs as

=VLOOKUP(What to find,In what data table, which column to return)

So you have a table that has

Client ref in column AClient name in Column BClient NI No in Column CClient SAUTR in column D

Row 1 has the column headings - Ref : Name : NI No : SAUTR

Rows 2 - nnn contains the details for each client with a line for each.

You want to be able to enter the client reference on cell F1 and have the name returned in G1 and SAUTR in H1

In G1 you type the VLOOKUP formula =VLOOKUP(F1,$A$1:$D$nnn,2,FALSE)

This will look for the client reference you type into cell F1 in the first column of the range $A$1:$D$nnn and when it finds the reference return the entry in column 2 (the names) at the row it found the reference.

In H1 you type the VLOOKUP formula =VLOOKUP(F1,$A$1:$D$nnn,4,FALSE)

This will look for the client reference you type into cell F1 in the first column of the range $A$1:$D$nnn and when it finds the reference return the entry in column 4 (the UTR) at the row it found the reference.

Adding FALSE as the final optional item of the formula forces VLOOKUP to find an exact match for the item it is searching for. This is useful when using it on a table that is not sorted. If not used then |VLOOKUP will return the first entry nearest the value it is searching for which could give an incorrect result.

If VLOOKUP does not find the value it is searching for it will return N/A# which will cause formulae based on it to also return N/A#

If what it is to return is a number to be used in another calculation - say for example you have a table with years in the first column and Class 4 NIC bands & rates in successive columns, and you wish the result of the VLOOKUP to return the rate for a year, but 0 if the year is not found - then you can expand the formula as follows

=IF(ISNA(VLOOKUP(F1,$A$1:$D$nnn,4,FALSE)),0,VLOOKUP(F1,$A$1:$D$nnn,4,FALSE))

In which case if VLOOKUP cannot find the item you are searching for and returns N/A# the ISNA part of the IF formula is TRUE so returns 0, otherwise the VLOOKUP result.

Now go and wrap a cold towel round your head & take two Aspirin :)

Thanks (0)