Perfect in Excel Vlookup Formula

Hi

I am writing this blog to help you in microsoft excel. This is my first blog. So today  I tell you about

Vlookup function or formula

What is Vlookup?

V stands for vertical. This formula or function used in to lookup for values. Now you may ask why do I need a function for this purpose. Now I tell you what is the use of this function. You can see, read and understand a small table data. But this function normally used when you have a huge data and you have small amount of time.

Below is  full formula :-


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Now understand the formula,

What is lookup_value:- The value to search in the first column of the table array. Lookup_value can be a value or a reference.

What is a table_array:- Two or more columns of data. Use a reference to a range or a range name.

What is a col_index_num:- The column number in table_array from which the macthing value must be returned.

What is range_lookup:- logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

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

As you can see in this image vlookup formula is applied in cell F1. 1. Here lookup value is order ID 10251 (you can select a cell as well, instead of putting any value where you can change value whenever you required) 2. Table array (range used for lookup value) is A1 to B6. 3. Column Index number is 2 this means it will give the value from column 2 or B in this case. 4. Range Lookup is set to false/0. You should also set it to false. Instead you want approximate result. False is for exact match.

If you are getting #NA error try below troubleshooting.

VLOOKUP isn’t  complicated,  but  it’s  easy  to  accidently  type  the  wrong thing or make  other mistakes  that  lead  to  errors  in  your formula. 

Now I tell you about correcting any mistakes in the  VLOOKUP syntax. Below are the steps which  describe  techniques  for  troubleshooting/correcting  #N/A  errors,  which  most  frequently  occur when  you’re  trying to f ind  an  exact  match. 

lookup_value

Does this value exist  in  the left most column  of  your  lookup table/Data Range?

 If not, and  it’s  not practical to move  the  column, you  must use  another  solution, such  as INDEX and  MATCH.

 Note  that the column's  physical  position  in  the worksheet doesn’t matter. If your lookup  table  starts  at column  R and  ends  at column  T, column  R is  the  leftmost column.

Does the format of  the lookup value match  the format of  the matching  value in  the lookup table?

 Errors  often  occur  when one  of these  values  is  not   formatted  correctly.

If your data contains spaces use trim function than use value paste and use this range as lookup value.

 If you’re using  text,  did  you  remember to  put quotation  marks around  that  text?

  If you’re  typing text directly (vs. using  a cell  reference  such  as  A2),  you  must  use quotation  marks. 

table_array

  Are you using  a relative reference (e.g.,  A2:G145)  when an absolute reference (e.g., $A$2:$G$145)  is necessary? This  is  important when  you’re copying  the  VLOOKUP formula to other  cells. 

In  this  case, you typically want to  “lock”  the lookup  table  to prevent misleading  results. To  quickly switch  between  reference types, select the  range  you entered  for  this  argument, and then  press  F4. Or, better  yet, use  a defined  name  instead  of a range; names  use  absolute cell references by default. Is your  lookup  table on  a different  sheet or  workbook?

 If so, is  it referenced  correctly in  this  argument?

 Doublecheck the  sheet names, especially if you’re  switching  back and  forth  between  sheets  as you’re  building  the  formula. 

col_index_num

Are you pointing  to  the   correct column  in  the lookup table?

  To  figure  out what this number  should  be, count over to the  right from the  first column in  your  lookup  table. Count that first column as 1.  Be  careful  here. You  won’t necessarily see  an  error  if you’re  off by a column, but Excel  may return  the  wrong data—e.g.,  March  sales  numbers  instead  of April  numbers.

  Do you see the #REF!  error?

  If so, make  sure  the  number  you specify for  this  argument isn’t greater  than  the  number  of columns in your lookup table. 

range_lookup

Have you swapped the   arguments? 

 Remember, use FALSE  for  an  exact match—e.g., when you’re  looking  up  a proper name or  specific  product code. Use  TRUE to  find  the  closest match  to the  lookup  value  if an exact match  doesn’t exist—e.g., when you’re  mapping  test  scores to a table  of letter  grades  or  income to a table  of tax  rates. 

If you’re using  TRUE, are the values in  the first column  of  your lookup  table sorted  in  ascending (A  to  Z)  order?

 If not, you  may see  unexpected  results. (This sorting  isn’t required  for  FALSE.) 

If you’re using  FALSE,  do  you  see a #N/A  error?

If so, Excel  can’t f ind  a match. This  may be  because  one  doesn’t exist. Or, a match  may exist, but Excel doesn’t interpret it as  such   because  of formatting  problems or  other  issues.

Comment if need help in any other Excel formula..