Member Avatar for masterjiraya

Problem:
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)

  • 2 Contributors
  • 2 Replies
  • 218 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by masterjiraya
Member Avatar for thines01

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

DW_451359.jpg 10.54 KB
Edited by thines01 because: clarity
commented: a very thankful reply. so simple answer. so friendly. Godspped to you my friend +2
Member Avatar for masterjiraya

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.