I am trying to compare one column of data against two separate tabs in a spreadsheet. I have been using two separate vlookup functions, and I have spent many hours trying to put it together into one function that works properly. I cannot figure out how to get rid of the REF or NA errors when a value is not found. Any guidance is greatly appreciated!! It is not required that a return value is shown - I just need to know if there is a match in either list, but I need to know which list contains the match. My current formulas are:

=VLOOKUP(I3, ListA, 3, FALSE)

=VLOOKUP(I3, ListB, 3, FALSE)Paula

As with all things Excel, there are many ways to accomplish your goals. I'm going to stick with VLOOKUPs, since that is where you have already started.

First, in general, you can use the ISNA() function to deal with the #N/A errors returned by VLOOKUP.

To return either No Match or Match, try:

=IF(ISNA(VLOOKUP(I3, ListB, 3, 0)),"No Match","Match")

To return the result of the VLOOKUP if there is a match, use:

=IF(ISNA(VLOOKUP(I3, ListB, 3, 0)),"No Match",VLOOKUP(I3, ListB, 3, 0))

Second, when searching two lists, to return No Match, Match In List A or Match In List B, try this:

Note: if there is a match in both lists, this is only going to return the first match found. If you need something that will tell you if there is a match in both lists, let us know.

=IF(AND(ISNA(VLOOKUP(I3, ListA, 3, 0)),ISNA(VLOOKUP(I3, ListB, 3, 0))),"No Match",IF(ISNA(VLOOKUP(I3, ListA, 3, 0)),"Match in List B","Match in List A"))

The theory here is that if the AND returns TRUE, then both VLOOKUPs returned #N/A, therefore there is No Match.

IF(AND(ISNA(VLOOKUP(I3, ListA, 3, 0)),ISNA(VLOOKUP(I3, ListB, 3, 0))),"No Match",

If the next VLOOKUP returns #N/A, then the match isn't in ListA, therefore it must be in ListB

IF(ISNA(VLOOKUP(I3, ListA, 3, 0)),"Match in List B",

If the VLOOKUP for ListA

didn'treturn a #N/A, then the match must be in ListA.

Thanks so much for your response. This has been extremely tricky to figure out, and we're not quite there yet. The large formula with the AND and ISNA you posted is nearly identical to my original formula, and this is exactly the point where I have been getting stuck.

The errors that I cannot figure out are:

1. It returns false "Match in List B" when there is actually a match in List A, however there are more than 255 chars in the cells we are comparing. I could understand showing a "No Match" in those situations, however it's erroring to "Match in List B".

2. It returns a false "No Match" when there is a match in List B. (cell chars much less than 255)

I have also tried the index and match functions to no avail either. I'm wondering if a macro is the way to go on this one....unfortunately I'm a bit of novice in that area.

I'm not trying to be critical, but take at look at your last response. You said that the formula I offered is "nearly identical" to your original formula, but you haven't posted your formula for anyone to analyze.

You then described 2 results that you say aren't accurate without posting any examples of data.

I'm sure you can see that without some more information, we haven't got anything to work with.

My apolgies for not being more clear. I used your formula this time around - and still have the errors I described that I am not able to clear. Here is your formula updated with my specifics....

=IF(AND(ISNA(VLOOKUP($I471,'confirmed list new.xls'!NAV,1,0)),ISNA(VLOOKUP($I471,'confirmed list new.xls'!NAV,1,0))),"No Match", IF(ISNA(VLOOKUP($I471,'confirmed list new.xls'!CV,1,0)), "No", "Yes"))

I see 2 issues: 1 - Unless I'm mistaken, your AND portion compares 2 VLOOKUPS that are both looking at the same list.

Don't they both look at 'confirmed list new.xls'!NAV,1,0?

That is going to give you a No Match if there is no match in the NAV list.

2 - Your other results are Yes & No, not the name of the list where a match is found - which is what you asked for

but I need to know which list contains the match.So...

Follow your IF's by reading them left to right, or use Tools...Formula Auditing...Evaluate Formula to watch the results as you step through the formula.

If the match isn't in NAV, you'll get No Match.

If the match is not in CV, you'll get No.

If the match is in both CV and NAV you'll get Yes.

Sorry for such a long delay - I have been unexpectedly away. First of all, thanks for the continual review. I appreciate it. I see that I did have an error in my formula as you pointed out in looking at the same table twice.

Though we seem to be saying slightly different things, my test data is showing that the formula is working as detailed here (which is what I want):

If there are no matches at all: I want "No Match"

If the match is in CV: I want a "CV"

If there is a match in NAV: I want a "NAV"I do not expect to ever find a match in both lists. Here is my current formula:

=IF(AND(ISNA(VLOOKUP($I500,'confirmed list new.xls'!NAV,1,0)),ISNA(VLOOKUP($I500,'confirmed list new.xls'!CV,1,0))),"No Match", IF(ISNA(VLOOKUP($I500,'confirmed list new.xls'!CV,1,0)), "NAV", "CV"))

I am still getting the false matches to "CV" when the cells have a large amount of data in them (over the 255 char max limit?). I can send you a file of mock data if you are interested. If it can't handle some of the larger cells I hope to show either "No Match" or introduce a new variable of "Too Large" Either way is fine for my purposes.

re: I am still getting the false matches to "CV" when the cells have a large amount of data in them (over the 255 char max limit?)Well, you've diagnosed the problem correctly - the issue is indeed caused by cells with more than 255 characters.

However, the formula is actually returning the "correct" result when it returns CV in these instances. By that I mean that the formula as a whole is returning the correct result because it evaluates all of the sub-parts and reaches the final

value_if_falseargument.If you look at your formula you will see that the

logical_testsof the IF's are ISNA. Therefore,anyresult of the VLOOKUP other than #NA is FALSE.If we don't get 2 #NA's to satisfy the AND then the first IF evaluates to FALSE and the formula moves on. If we don't get a #NA to satisfy the second IF, it evaluates to FALSE and returns CV, just like it (the formula) is supposed to.

So, the question becomes "Why is the formula making it all the way to the final

value_if_falsewhen we know there is a match in NAV (or possibly no match at all)?That, my friend, I'm going to leave to you to answer. I will, however, give you a hint as to how to do that:

Set up your spreadsheet to get what you called a "false match" of CV by using more than 255 characters. (Of course, you now realize that it isn't really a "false match" - it's merely a nested IF whose multiple conditions were never TRUE)

Now select your formula and pull down Tools...Formula Auditing...Evaluate Formula. Click the Evaluate button and watch as the formula resolves itself piece by piece.

I think you'll see why I say the formula (as a whole) is working correctly when it returns CV when the cell contains more than 255 characters.

Thanks for the advice. Unfortunately we are now to the point where I become lost. I have absolutely no idea how to proceed or fix this. I have evaluated the formulas many, many times. Unfortunately I have had no eureka moment. I am not very skilled when it comes to evaluating the formulas in excel. The only difference I can see when evaluating the formula is that the over 255 char cells evaluate to #value instead of #ref. Beyond that, it all looks the same to me.

You have found the problem, now you need to find a solution. As you saw when you evaluated the formula, the VLOOKUP returned #VALUE if

lookup_valuewas longer than 255 characters, This happened regardless if there was a match or not because the VLOOKUP never really looked anything up. It simply cannot handle alookup_valuelonger than 255 characters.So, put a #VALUE error inside an ISNA() function and of course you'll get FALSE because #VALUE is not #NA. Since every VLOOKUP in the formula will return #VALUE, all the ISNA()'s will return FALSE. The formula then does what it is supposed to do and goes all the way to the last

value_if_falseargument and returns CV.Fixing the problem depends on what your data looks like. Could you get away with just checking the first 255 characters when looking for a match? Could you add a column in front of your

lookup_tablesNAV and CV and create a column of values like =LEFT(A1,255) and then use VLOOKUP(LEFT(I500,255)...etc?As long as the first 255 characters aren't the same in multiple cells, that should work.

Another option would be use ISERR() to test for the #VALUE error and return return Too Long if that error shows up. Look at the IS functions in Excel help and see if you can figure out how to get the ISNA and ISERR functions to work together.

Outstanding!! That was exactly the lightbulb moment I needed. In this particular case, I can get away with looking at the first 255 chars, but it's great to know both options are there. Thanks again!

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History