What is VLOOKUP
VLOOKUP is a most useful formula in Excel. Unfortunately – it is also one of the most confusing for the beginner. In simple words, actually VLOOKUP function search a value in the range which mentioned in the formula.
The VLOOKUP function can sometimes be a better solution in a scenario. You can see a really good example of where you should learn how the VLOOKUP function works in one of the comments below.
That’s not to say that VLOOKUP is automatically a better solution:
According to Excel’s formula description, VLOOKUP looks for a value in the workbook, worksheet, table, array or in a column of a table, and then returns a specific value in the same row from a column you specify.
HOW VLOOKUP WORKS
If you want to pick up a value from other sheet then VLOOKUP function is the most useful for that.
To dumb it down for you, VLOOKUP lets you pull information about your selected cells into your current sheet, from other sheets or workbooks where that value exists.
The formula for VLOOKUP looks like this:
Actual figure of function will look like this.
VLOOKUP WITH IF STATEMENT
What this formula says :-
It’s mean if A2 have any value then search that value in cloumn 2 of MASTER sheet, FALSE uses for exact value.
VLOOKUP WITH MATCH
This table is used to find a value based on a specified name and month.
The =VLOOKUP() is used to scan down to find the name.
The problem arises when we need to scan across to find the month column.
To solve the problem the =MATCH() function is used.
The =MATCH() looks through the list of names to find the month we require. It then calculates
the position of the month in the list. Unfortunately, because the list of months is not as wide
as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.
The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.
The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.