Vlookup in Excel is one of the most powerful functions available because it can draw information from a table array in a matter of seconds. However, one of the problems is that sometimes you will get errors from a vlookup and this even can happen sometimes when the value is actually in the present in the array so why does this happen?
If a vlookup returns the value #N/A it means that as far as Excel is concerned the value is not present in the table array which can happen for a couple of reasons. The first and most obvious is the value is not actually there even if it appears to be present. This can occur for a couple of reasons.
The first reason is that the text that you are looking at may have a space on the end of it which you cannot see it but Excel will not view it as an exact match and therefore produce an #N/A when a vlookup is used. The second reason why you may not get a match is that the text present is not actually the same, which can occur in cases where you are using things like capital O and zero.
The first two reasons can be tested fairly easily in Excel. If we use an example that the lookup value is in cell A1 and the value in the array is in Cell F1. To test if these cells are the same simply.put the following formula into a cell =A1=F1 on the spreadsheet. If the values are a match the cell will return the message ‘TRUE’ and if they are not a match or return the value ‘FALSE’.
The 3rd reason this may occur is because the formatting within the cells is different therefore Excel views the two entries as being different and returns an error. This is a less common reason for it to occur but it is certainly happened to me at least a few times. Typically, if this is a problem you will see multiple cells with #N/A in them.
How To Fix The Problem Where Formatting Doesn’t Match
To fix the problem either what the video below or follow the directions below.
Step 1 Highlight the column that the vlookup values in and select text to columns which is located in the excel’s ribbon in the data tab.
Step 2 Click Next
Step 3 Click Next.
Step 4 Select Text and click finish.
Step 5 Repeat these steps on the first column of the array. Matching the formats should fix the problem.
How To Eliminate #N/A Using Logic Functions
The other method to eliminate the appearance of errors within VLookups is to use logic functions which can be done in a couple of different ways depending upon the reason for the error.
Method 1 Method 1 is suitable for any errors that occur within vlookups however the complexity of the formula is higher compared to methods 2 and 3. The formula =vlookup(A1, G1:H100, 2, FALSE) is producing an error. To stop that error a nested function can be created using an if function and an Iserror function. =if(ISERROR(vlookup(A1, G1:H100, 2, FALSE)), “”, vlookup(A1, G1:H100, 2, FALSE) ) ISERROR Function: ISERROR(vlookup(A1, G1:H100, 2, FALSE)) The function will produce either TRUE OR FALSE IF Function: =IF(Logic Test, TRUE, FALSE) The if function uses the ISERROR as the logic test. If TRUE (there is an error) leave the cell blank (“”) If FALSE (no error) complete the vlookup
Method 2 Methods 2 and 3 are suitable if the reason the error is occurring is because there is a blank cell which is not in the array and therefore does not get a match. =if(A1=””, “”, vlookup(A1, G1:H100, 2, FALSE)) IF Function: =IF(Logic Test, TRUE, FALSE) The if statement uses the A1=”” to check if the cell is blank. If TRUE (if lookup cell empty) leave the cell blank If FALSE (lookup cell contains data) complete the vlookup
Method 3 Method 3 is similar to method 2 accept that it uses the isblank function to test whether the lookup cell is empty. =if(ISBLANK(A1), “”, vlookup(A1, G1:H100, 2, FALSE)) ISBLANK Function: ISBLANK(A1) The function will produce either TRUE OR FALSE IF Function: =IF(Logic Test, TRUE, FALSE) The if statement uses ISBLANK to check if the cell is blank. If TRUE (if lookup cell empty) leave the cell blank If FALSE (lookup cell contains data) complete the vlookup