Excel - Data Matching 1

[elfa]

Hello,

If I have two colums of data such as the below: (assume they are in column A and B)

Table 1 Table 2

1 12
2 13
3 3
5 4
5 5

How could I get a yes or no to display in column C based on a line in Table 1 matching a line in Table 2?

Regards
Elfa.

 

[maudedo]

If in excel,

does it have to be the same row. if so...
in c1,say =if (a1=b1,"yes","no"). then drag down (autofil) until all rows have the same formula.

 

[elfa]

Hello thanks Maudedo.

Needs to be different lines.

Is there a line anywhere in table two that matches table one. Like a one to many.

Regards
Elfa.

 

[maudedo]

Hi Elfa
I have the code at work. I'll play around and get back to you soon.
regards
Donna

 

[GeekGirlau]

=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),"No","Yes")

 

[TheBitDoctor]

Elfa:

Enter this formula in column C:

=IF(ISERROR(VLOOKUP(B1,$A$1:$A$10,1,FALSE)),"No","Yes")

If this formula is in cell C1 then it will return 'Yes' if the value in cell B1 is anywhere in the list contained in column A. If you want to search all of column A and not just cells 1 through 10 then use A:A for the second arguement of the VLOOKUP function. Be careful though, if you have a hundrend items in your second table then you could potentionally be doing 6,500,000 comparisons in addition to calculating the rest of your sheet.

Also, this works well for integers or Text but if your table contains any calculations your should round off to remove floating point decimals. Try comparing 12.333333333333 and 37/3 to see what I mean.