How to correct #N/A error in excel

Question

I have several columns of data. One is a list of over 1000 names and other associated data. The other is a list of names with a numerical value associated with the names. Not all of the names in the first list are in the second list and vice versa. I am trying to use vlook up to get the the numerical value associated with the second list matched up with the names in the first list. This is the formula I am using VLOOKUP(B2:B1201,F2:G1133,2,FALSE).

Some of the numerical data transfers fine, but for others I am getting the #N/A error. I found that if I copy the name from the second list into the appropriate spot in the first list the error goes away. However with several hundred #N/A errors, this is not feasible. The entries in the two lists look visually identical and I have tried trim and clean to avoid unseen differences.

Any thoughts on what else I might try is appreciated.

Tom

Answer 1

Hi,

I'm Sneha and I'd be happy to help you out with your question.

It sounds like you are trying to use VLOOKUP to match names in one list with corresponding numerical values in another list. If you are getting #N/A errors, it could be because the names in the two lists are not an exact match. VLOOKUP only returns a result if the lookup value is exactly equal to the value in the first column of the lookup range.

There are a few things you can try to troubleshoot this issue. First, you can try using the TRIM function to remove any leading or trailing spaces from the names in both lists. This can help ensure that the names are an exact match. You can use the formula =TRIM(cell) to remove extra spaces from a cell.

Another thing you can try is using the IFERROR function to handle the #N/A errors. IFERROR will return a specified value if a formula returns an error, so you can use it to return a blank or a default value instead of the #N/A error. For example, you can use the formula =IFERROR(VLOOKUP(B2:B1201,F2:G1133,2,FALSE),"") to return a blank value instead of the #N/A error.

You can also try using the MATCH function in combination with VLOOKUP to find approximate matches. MATCH can be used to find the position of a value in a range, and then VLOOKUP can be used to return the corresponding value in a different column. For example, you can use the formula =VLOOKUP(MATCH(B2:B1201,F2:F1133,0),F2:G1133,2,FALSE) to find the approximate position of the lookup value in the first column of the lookup range, and then use VLOOKUP to return the corresponding value in the second column.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Answer 2

Hi,

This should work. Copy the formula down

=xlookup(B2,$F$2:$F$1133,$G$2:$G$1133,"")

Hope this helps.

Answer 3

Thank you for your response. While your formula did away with the #N/A errors, they were replaced with empty cells.

The lists of names I am working with came from two different sources. I did a test and copied the same name from each list, then ran the find function. It only found one of the names. I tried clean and trim, but this did not help.

There is something going on with the different sources, but I can't figure it out.

Thank you for taking the time to try and help me.

Answer 4

Thank you for taking the time to respond. Unfortunately, your solutions did not solve my issue. The data I am using is from two different sources and this seems to have something to do with the problem.

In any event this is not important enough of a issue to expend more time on. I appreciate your effort to assist me.

Answer 5

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

The #N/A error in Excel can occur when a lookup value is not found in the lookup table. In your case, this may be happening because some of the names in the first list do not exactly match the names in the second list, even though they may look visually similar.

One solution to this problem is to use the IFERROR function in your VLOOKUP formula. This will return a blank or a custom value instead of the #N/A error when a lookup value is not found in the lookup table. For example, you could use the following formula:

=IFERROR(VLOOKUP(B2:B1201,F2:G1133,2,FALSE),"Not Found")

This formula will return "Not Found" instead of the #N/A error when a name is not found in the second list. You can replace "Not Found" with any custom value you want.

Another solution is to use the EXACT function in your VLOOKUP formula to compare the names in the two lists exactly, without ignoring any spaces or other differences. For example, you could use the following formula:

=VLOOKUP(EXACT(B2:B1201,F2:F1133),F2:G1133,2,FALSE)

This formula will compare the names in the first and second lists exactly, and return the associated numerical value from the second list if a match is found. If a match is not found, the formula will return the #N/A error.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha