VLOOKUP in Excel – Problems and Troubleshootings

Excel VLOOKUP function is used to lookout and retrieve the information from a data table. However, sometimes you may find that even if you have done everything right, VLOOKUP does not work. There are a few common problems due to which it will return #N/A error. I am listing down the common problems and their troubleshooting. Try the following solutions and I am sure, it should start returning you the correct value.

Formula not correct

Believe it or not, in most of the cases, the main problem is the incorrect formula. A simple mistake can prevent the formula to fetch the correct result. So, make sure that you have written the correct syntax – see the comma, spelling mistakes, brackets, extra spaces, etc. first.

VLOOKUP Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Just make sure there is no additional space, you have used comma at appropriate place and most importantly not missed the last optional range_lookup value. In most of the cases you will pass the value “FALSE” for it. Another thing to keep in mind is that the lookup_value must be in the first column of table_array. Use insert function dialog box instead of writing to code manually to avoid typing mistakes.

Use of Relative Reference

In practical scenario, most of the times you will write the formula in one cell only and then drag it to the other cells. If you are not using absolute reference, the table_array will get changed along with lookup value as you drag it. But for most practical purposes, your table_array should be same. This will lead to getting #N/A error in many of your cells. Let’ understand it with this example.

VLOOKUP absolute Reference Example

Here, we want to get the cost of Pant, Belt and T-Shirt from the table_array (A2:B7). For cell E2, we write the following VLOOKUP Formula.=VLOOKUP(D2,A2:B7,2,FALSE)

Now, for cells E3 and E4 where we want to get the cost of Belt and T-Shirt, we simply drag to copy the formula. But as you can see, we are getting the “#N/A” error in cell E4.

This is because, once you drag to copy the formula, the table_array also gets changed. Look at the formula in E4 cell.

Relative Reference in VLOOKUP

Problem of Relative Reference in VLOOKUP

=VLOOKUP(D4,A4:B9,2,FALSE)

It has changed from A2:B7 to A4:B9

To overcome this issue, you need to use the absolute reference. For doing so, select the table_array (A2:B7) in cell E2 and press F4. You will find “$” before A, 2, B and 9. It means that the range is now fixed and will not change when you drag the formula.

Absolute referencing with F4

Use F4 for absolute reference in VLOOKUP

Press F4 again and the “$” before A and B will disappear. It means, A and B (columns) will change but 2 and 9 (rows) will always be constant while you drag. If you press F4 once again, the $ sign before 2 and 9 will disappear and appear again for A and B. Now the rows can change but the columns are constant. Do some experiments and lookout in the formula after dragging to understand it once for all.

Format Change – Numbers stored as Text

This is another common problem. Many times, the lookup value which is a number is stored as text in the table_array. VLOOKUP is format sensitive, so you will get #N/A error.

See the following example:

Number stored as text in VLOOKUP

Use of Correct Format

Make sure that the format of lookup value and the value in the first column of table_array is same. To change the format of number stored as text to numbers, select all the affected cells, click on the warning icon before it and select convert to number. However, if you don’t want to change anything in your table_array, you can just modify the formula a little.

=VLOOKUP(TEXT(D2,”0″),A2:B5,2,FALSE)

Here we are first converting the number in D2 cell to text to match it with the table_array. Now since both the formats are same, you don’t get any error.

Lookup Value is not in the Left most column

As I mentioned it earlier, the lookup value must be in the first (left most) column of your table_array. If it is not the case, you may use INDEX-MATCH functions together. This is a good VLOOKUP alternative and you can go through my INDEX MATCH Functions tutorial.

Spaces before or after lookup value

There may be some spaces before or after lookup value. Solution is very simple – use TRIM() function to remove the extra spaces from both lookup value and first column of table array. How do you check the spaces? Select the cell with value and hit F2 button to inspect the value in formula bar.

Removing #N/A Error

In some cases you get genuine #N/A (value not available) errors. If you don’t want this error to appear and simply want a blank column instead, you can use the following trick to remove this.

=IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)=TRUE), “”, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))

Conclusion

These are some of the common reasons of Excel VLOOKUP formula not working. So, next time when you get stuck, check the above troubleshooting first. Even after the above solutions, if it does not work, let me know in the comments below- we will try to come out from it together.

How to copy Excel Chart without links

Sometimes you may require to copy chart from one excel book to another. You right click on chart, copy and paste it on another workbook. And the chart appears to be pretty well copied. But what about the data necessary for creating this chart?

After you save the excel file with copied chart and move it to some other folder, the problem starts when you try to open it again. If you are using Excel 2003 or earlier version, you get the following message.

excel-update-links.gif

Now, suppose we want to change a few data in a series. Right click on chart, go to the data source and try to edit any series. What happens? You get an error – “Reference is not valid. Reference must be to an open worksheet”.

copy-excel-chart

When you copy the chart from one excel workbook to another, the chart takes reference data from the original workbook. To make any changes, your original file must be available at its original location. At the same time, it must be open also so that you can make required changes to the data source.

What if you wish to copy only chart without any reference and links to original file? Sometimes back, I was facing the same problem. When I searched for the solution, I found that most of the people advising to copy the source data in another worksheet along with the chart. After that, make changes to the data source of the chart. Though it works, it is not the efficient solution and is time consuming. One another solution is to paste the chart as an image. It may be a good option if you wish to take just print-outs.

Thankfully, I could get the solution from one of my colleagues. So, this is the Excel TipDelink Excel Chart Data!

De-link Excel Chart Data

You can’t do much at the time of copying the excel chart. But after copy, you can delink your chart data from original file. How?

edit-links-excel

In the Data Ribbon, go to connections sub-group and click on “Edit Links”. In the ‘edit links’ pop-up window, select the source listed as the original excel file and click on ‘Break Link’. You will be asked for the confirmation. Confirm by clicking on ‘Break Links’ again.

break-links-excel

That’s it. Your chart is now free from all the references and links. Try to edit any series now. All the references have been converted to arrays of data. It has absolutely no relation with original file; you can move this file wherever you wish.

This simple tip can save you from the hassles of copying the sheet with chart data along with chart. At the same time you can make changes to the chart data as per your requirements.