VLOOKUP FUNCTION SYNTAX IN EXCEL

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.

Example:

The Formula

The formula for VLOOKUP looks like this:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Actual figure of function will look like this.

=VLOOKUP(A2,MASTER,2,FALSE))

 

VLOOKUP WITH IF STATEMENT

=IF(A2=””,””,VLOOKUP(A2,MASTER,2,FALSE))

 

 vlokup

What this formula says :-

=IF(A2=””,””,VLOOKUP,A2,MASTER,2,FALSE))

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.        

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *