Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm attempting to compare two columns of data. One column (column G) is a "master" list with all account numbers in it and the other column (column A) only has a partial list of account numbers.

I'm looking for a formula that will compare the two columns and return a true if found in both columns or a false if only found in one.

share|improve this question
    
If G is a master list, everything should always be found in it, correct? So a simple vlookup comparing column A cells to the column G content should suffice. If not, the likely culprit is as Bob mentions the content having spaces, or perhaps being string to numeric comparisons. a small sample might help determine that –  datatoo Apr 19 '12 at 14:52
add comment

2 Answers

up vote 1 down vote accepted

You are probably going to want some variant of VLOOKUP to get this done. The trick is that you want your VLOOKUP to return a true or false. My method for getting a true/false from VLOOKUP is this:

=IFERROR(VLOOKUP(lookup_value,lookup_range,index,FALSE)>0,FALSE)

this returns true if it finds a value, and false if it doesn't. (if someone knows a better way to do this, I'd love to know it!)

So now you put one of those statements for each of your columns inside an AND statement & you should have your test!

=AND(lookup test1,lookup_test2)

That was kinda long, but I hope it helps!

share|improve this answer
    
Thanks Roger! I know this is a Excel 101 question, but I'm more than a bit rusty w/ formulas, so here goes: I've populated my lookup value and range, but what do I designate for the index? I'm also confused on the =AND statement and how I incorporate that in my formula. Thanks again for the help! –  Bob Apr 13 '12 at 18:02
    
Usually VLOOKUP is used where you want to find a value in one column and return a value in another column (like looking up a name & then returning a phone number), so the index tells tells excel which column the phone number is in. In this case you just want to return the same value you are looking up.... so put 1 in the index. office.microsoft.com/en-us/excel-help/… –  Roger Apr 13 '12 at 18:07
    
Thanks Roger---still no luck in getting it to work. Would it be possible to send it to you to see if I have it set up correctly? –  Bob Apr 13 '12 at 18:13
    
Try breaking the formula out into 3 cells. One for each VLOOKUP function, and then another for the AND statement. that way you can test the parts of the formula. The vlookup statements should look like this =IFERROR(VLOOKUP(A1,B:B,1,FALSE)>0,FALSE) –  Roger Apr 13 '12 at 18:20
    
Also feel free to post your formula. Want to make sure future readers of this post get the benefit –  Roger Apr 13 '12 at 18:23
show 3 more comments

@Roger's answer is very close, but just a little bit off.

IFERROR does not natively return TRUE/FALSE. It returns a specified value if the given formula results in an error, or the formula's result if there is no error.

Examples:

=IFERROR(1/0,"Error") returns "Error"
=IFERROR(4/2,"Error") returns 2.

The way I get TRUE/FALSE from a VLOOKUP is a combination of IF, IFERROR, and VLOOKUP:

=IF(IFERROR(VLOOKUP(lookup_value,lookup_range,index,FALSE),1)=1,FALSE,TRUE)

This says "If the VLOOKUP returns an error, return FALSE (i.e.: target not found), else return TRUE (i.e.: target found)."

share|improve this answer
    
I've recently also discovered ISERROR, which can shorten this up a bit. Will edit later. –  Iszi Oct 22 '12 at 21:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.