How to vlookup

How to do a Vlookup in Excel

[mks_tabs nav=”horizontal”]
[mks_tab_item title=”Overview”]
Do you have hundreds of rows of data that need to be sorted accurately and immediately? Then learning how to VLOOKUP can literally save you hours of work!

Be proficient in using VLOOKUP function in minutes with these simple tutorial which works in all versions of Excel and Google Sheets. Save hours of analysis and finish your work early. Follow these 5 steps on how to VLOOKUP within minutes.

STEP 1 First, click into a cell where you want the VLOOKUP result to appear.

STEP 2 Click on the ‘fx’ button above the column of the cell you clicked in Step 1. The Insert Function dialog will appear. In the Search for a function: text box, type “vlookup” and click Go. Highlight VLOOKUP and click OK and the function arguments window will pop-up.

STEP 3 Add the VLOOKUP syntax tag. Type in =VLOOKUP( to start the VLOOKUP syntax. The rest of the syntax will go between the open parenthesis and a closed one at the end.

STEP 4 Find the cell in which the lookup value is written, then enter the cell’s name into the VLOOKUP syntax followed by a comma.

i.e.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP Function Arguments

• Lookup_value – is the corresponding value from the first table which we want to find in the particular column of the second table.

• Table_array – is the entire table’s span from the top-left cell to the bottom-right cell (not including headers) which has the corresponding numbers that the lookup value is looking.

• Col_index_num – is the number of the column on your lookup table that has the information you need.

• Range_lookup – defines how close a match should exist between your Lookup_value and the value in the leftmost column on our lookup table. In most cases, using the word “FALSE” will allow VLOOKUP to find the exact value in your specified column for the selected item

STEP 5 After you have entered the necessary values for the VLOOKUP syntax, type in FALSE) to close the formula.

Now all you have to do is drag down the formula, so that the cells below populate, and you save yourself the hassle of manually finding the corresponding value for each and every cells in the first table.

video by Excel Stuff

[/mks_tab_item]
[mks_tab_item title=”Transcript”]

Do you want to use Excel more efficiently?
Do you want to be able to go home earlier?
If you answered yes to either of these questions, then you need to watch this video!
The vlookup is the most useful, yet most mis-understood function in Excel.
This is a step by step vlookup tutorial (only 7 steps) which works in all versions of Excel.
It will save you hours with analysis! vlookups are usually done between different
spreadsheets, but to make it easier to show how they work, I’ve put the data on to one
spreadsheet The tables in the spreadsheet have some of
the most famous 100m runners in the world – including Usain Bolt (the World Champion!).
The runners are numbered from 1 to 10 in column B (the numbers are for illustrative purposes
only).
I’ve put the names of the runners / athletes in column C.
IMPORTANT: You should note that the table on the LEFT HAND SIDE is set in ASCENDING
NUMERCIAL order ie numbers 1 to 10.
However, the table on the RIGHT HAND SIDE is arranged in ALPHABETICAL order of the names
of the athletes.
So the numbers in that table DO NOT go from 1 to 10 in ascending order.
We’ll use the vlookup function to get the ‘Prize Money’ each athlete has won in
column J in the second table into column D in the first table.
STEP 1 First, click into cell D2 – as this is where
we want the first vlookup result to appear.
STEP 2 Click on the ‘fx’ button above column
B – many people start by typing “=vlookup…” but you don’t have to!
Clicking the “fx” button is much quicker! (you will see the ‘Insert Function’ table
pop up – From this window, we will select the vlookup
function, as it is one of the ‘most recently used’ functions in the category here.
If the vlookup function isn’t one of the ‘most recently used’ functions, then change
the “Or Select a Category” field to ‘All, then scroll to the bottom, where you will
see the word ‘vlookup’ and click on it.
Click ‘ok’ and the function arguments window will pop up.
This table will show the Syntax (a fancy word for the format of the formula) and it will
display the different parts of the syntax ie VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
STEP 3 Here, you should click into cell ‘B2’
because that’s the first number or reference for which you want to lookup a value.
The ‘look up value’ is the corresponding value we want to find in the first column
of the second table – so we want to find out what ‘Prize Money’ the runner in position
1 got in the table that spans columns H to J.
STEP 4 Now click in the next field, the ‘table
array’ field (a table array consists of two or more columns of data and the first
column in the table array – in this case col.
H – has the corresponding numbers that the ‘lookup value is looking at ie the runner
positions in this case).
STEP 5 After clicking in the ‘table array’ field,
you then need to highlight the columns that that you want to look up data from; so here,
we will highlight columns H to J, because our lookup value column starts from column
H in the second table, and the Prize Money which we want to check is in col.
J An alternative way to populate the “Table_array”
field is to highlight the range of data you’re looking up, starting with your first unique
value – in this case cell H2.
So you’d highlight cells H2 to J11, because J11 is the last cell in the range.
You’d then need to FIX this table array by putting dollar signs in before the H, before
the 2, before the J and before the 11, so your formula at the end looks like this: =VLOOKUP(B2,$H$2:$J$11,3,FALSE).
If you’re in the table array field and you press F4, then Excel will do this automatically
for you.
This is useful to know if the spreadsheet doesn’t allow you to highlight cells H to
J because some cells are merged or you’re getting an invalid error.
But for now, just leave it, ensure, you’re fields look the same as those in the screenshot
above then proceed to Step 6, the second last step.
STEP 6 When you highlight the columns, you should
note that column J is the 3rd column from column H.
We then move onto the next step, Col_index_num, which is the column Index number.
Click in that field and type the number 3) – this is because column J is three columns
away from column H.
STEP 7 Now, for the last bit, simply click in the
‘range lookup’ field and type in the word false.
You should always type in the word false here, because we want the vlookup to return an exact
match for what we‘re looking for, and if it doesn’t then we want it to return the
word false.
Now click ‘Ok’, and like magic, you will notice that the vlookup has returned the figure
of $1,000,000 against Usain Bolt’s name in the first table.
If you look at the second table, the figure of $1,000,000 is also against Usain Bolt’s
name, so we know the vlookup has worked.
And that’s how you string together the vlookup formula in excel.
Now all you have to do is drag down the formula, so that the cells below cell D2 populate,
and you save yourself the hassle of manually finding the corresponding Prize Money value
for each and every athlete in the first table.
In a work scenario, if you had hundreds of rows of data, this would literally save you
hours of work!
Congratulations on finishing the tutorial!
If you want to try out a vlookup between two separate workbooks, you can do so here: http://howtovlookupinexcel.com/vlookup-between-two-workbooks
If you want to learn about Pivot Tables, you can do so via this website we created following
user demand: http://pivottablesinexcel.com/ If you have problems with your vlookup click
here – 10 common problems: That’s all for now.
Please subscribe or put comments in the video below to let us know what you thought.

[/mks_tab_item]
[/mks_tabs]

(Visited 4 times, 1 visits today)

Comments

You might be interested in