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?
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.
If you are getting #NA error try below troubleshooting.
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..
