Vlookup not returning correct value
There are a number of reasons why the VLOOKUP formula may not be returning the correct value. Some we have covered in other areas but below is as comprehensive a list as we can think of based on what you are seeing
VLOOKUP Returns a value but it is incorrect
The first situation involves VLOOKUP returning a value but it is not what you expected or incorrect. There are a number of possible reasons for this:
- You didn’t specify whether Excel must do an Exact or Approximate match (true or false as the last criteria)
- You specified that Excel does an approximate match but you should have required an exact match. As shown below we are expecting 34% and 45% but getting 75% for the two different students. This is because in the first case we omitted the TRUE or FALSE (and Excel’s default is an approximate match), and in the second case we specified a TRUE but should have specified a false. As a rule, if you want Excel to either return an answer or tell you there is no answer you must use FALSE.
- You correctly used TRUE for an approximate match but your didn’t sort the table in ascending order. See below that for the marks we have (incorrectly) included 75% in the middle and 45% at the end. The result is that a 75.3% only scores a C. The lookup table has to be sorted in ascending order for the TRUE to work correctly.
- You correctly used TRUE for an approximate match but you tried to manually sort an alphanumeric based table. As shown below, cell B11 is giving an answer which is not the one I want. As a human the sort order of the first table seems reasonable as it is increasing based on the number part of the alphanumeric. However, in Excel’s view the correct sort order of an alphanumeric is shown at the bottom where it treats each item as a word. Rather sort these types of tables with the Sort tool and don’t try to manually place an item in the table where you think it belongs.
- The lookup value appears twice in the table and you were expecting to see the second result and Excel pulled the first result. As shown below, student A111 appears twice with two different results. Excel will always pull the first one it sees, so if the second one is actually the correct answer you will have an error. This is a data issue and must be addressed in the data source.
VLOOKUP result shows the formula and not the result
This is quiet common when you have downloaded a file from another system and now you want to work with it in Excel.
As shown below, instead of giving a result, it shows the formula instead.
This is because the cell format is set as text. You can change the format of the cell to General and when you click in the cell and click enter the formula will work. However, if you have lots of VLOOKUP’s you need to correct, highlight them all and convert to a general format. Then instead of clicking into each one, use the FIND/ REPLACE tool to replace all the = with = (same character). This forces Excel to go into each cell and make a change but without changing the formula.
VLOOKUP returns an error message but you can see the answer yourself
In this situation you can see the answer you are expecting but the VLOOKUP is giving you one of the error messages. This could be because of (these are all covered in the previous lessons but repeated here for completeness):
- It might not be there- Excel may be correct and it may not be in the list. You should look in your table and see if you can see the lookup value yourself. If you can then it must be one of the reasons below. If you can’t then Excel is probably correct. As shown below, the student A150 does not exist in the table.
- The lookup value and first column in the table array are not the same. Most common reasons for this are:
- spaces before or after words. Check for this first as it is very hard to see. Notice below that we can see A111 in the list but VLOOKUP is giving us an error. If you look closely at the value in cell A8 you will see that at the end there is a space whereas in A14 there is no space. In Excel’s world “A111” and “A111 ” (note the space at the end) are different.
- different formats of the data. If the lookup value is a number e.g. 1234 but in the table it is shown as 1234 but treated as text (left aligned), then Excel won’t find a match. Below note that the number 1100 in the table is left aligned and the cell is formatted as text. The lookup value in A14 is formatted as a number (right aligned). For Excel these are not the same thing hence the error.
- your table array is wrong. Most likely reasons are:
- your lookup value is not in the first column of your table array but in another column. As shown below, the formula is looking at the table from A4 to D8 but the lookup value of 1100 can only be found in the second column. Remember Excel only looks for the value in the first column it sees. It only uses the other columns to pull information back. In this case the table should be $B$4:$D$8
- You didn’t use dollar signs on the table array so as you copied it down, where Excel looked for the table array is incorrect. Notice below that because there are no dollar signs on the table, by the time it is copied down to cell B17, the table no longer contains the value we are looking for.
- You lowest lookup number is lower than the lowest number in the table. As shown below our table starts at 10% but we are looking for 5% in the table. Make sure that no value you are looking up is lower then the lowest value. If you have to, start the table with an impossibly low number.
- You didn’t use dollar signs on the table array so as you copied it down, where Excel looked for the table array is incorrect (same as the same issue for an exact match).
- #Name? errors occur if you type the word VLOOKUP wrong (C14 below) or if you don’t use quotes when Excel expects it (in C15 below we need to use quotes ” ” around the Adrian word). This applies when you are referring to a particular text item. If you have a named ranged called adrian then this would work.
- The #Value error occurs when one of the cells referred to contains more than 255 characters. In the example below, the lookup value in A14 contains over 255 characters and the net effect is that the VLOOKUP returns a #value error. This can happen when you are using unique references that have lots of characters. A more likely case is where you are using multiple criteria by concatenating cells to create a unique lookup value.
- #REF! occurs when you are asking Excel to look for a column number (3 in the image below) that doesn’t exist in the table array. So in this formula we have told Excel that the table array is from $A$4 to $B$8 (blue lined box) which is only 2 columns, yet we are asking Excel to return the 3rd column.
- If you see a #REF error in the cell value (cell B14 below) and when you look in the formula you also see a #REF error, it means that a cell or cells that the VLOOKUP was looking at has been deleted.