I have a version of 2003 of MS EXCEL

for example I have Line A and Line B in the same work sheet

the DATA of Line A has a b c d e and f

the data of Line B has b and d only

if I use this formula Vlookup($A:$A,$B:$B,1,FALSE), it returns b and d in line C but what I want is th the a c e and f

how would I do that.

in discerete Mathematics, vlookup() is the INTERSECTION of 2 sets but what I want is NOT the INTERSECTION of two sets

in my original formula, it has a wild card ( meaning to say... ""&$A:$A&"" was used in vlookup)

5 Years
Discussion Span
Last Post by masterjiraya

If Cell C1 contains the formula =VLOOKUP($A3,$B$1:$B$6,1,FALSE)
then the result in Cell C1 will be "#N/A"
You can then choose to filter on the "#N/A" values or you can use IsError() to convert them to user-friendly values:
=IF(ISERROR(VLOOKUP($A1,$B$1:$B$6,1,FALSE)), $A1, "")
=IF(ISERROR(VLOOKUP($A2,$B$1:$B$6,1,FALSE)), $A2, "")

Which means: if the result is not found, show the value of the original cell, otherwise show blank.
When the formula is pasted all the way down for each value, the result will be a,c,e,f

Edited by thines01: clarity

Votes + Comments
a very thankful reply. so simple answer. so friendly. Godspped to you my friend
Attachments DW_451359.jpg 10.54 KB

I'm so very thankful to you Mr./Ms. thines01. from your formula posted above I have finally concluded that I in clude the NOT() before the IsError() so the results can only be seen in Line C. A very greatful help from you Mr./Ms. thines01. Now the discrete Mathematics' NOT INTERSECTION in the sets of data is now possible.

I'm so surprised that Excel has now error handling functions as of this version, because what I know before was ISNA(). I'm so ... noob, that I was left far behind.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.