If Value X exists, say yes or no

[oliviar]

Hi guys,
I need to create a method of going down column b, and looking at the value in column A, and seeing if it exists in a table I have on another sheet. If Value A exists, then I want ColB to say yes or no.

It seems like it should be so simple, but its killing me!

 

[diddi]

what column is the table on the second sheet in

 

[oliviar]

5, or E
starting in row 2

 

[diddi]

=IF(ISNA(MATCH(A1,$F$1:$F$100,0)),"No","Yes")

fill down

you will need to change the F1:F100 reference to your new sheet

 

[oliviar]

Thank you Diddi,
You da man!
I was trying so many combinations of match, find, index, vlookup... i was just in circles...

 

[diddi]

so did it rain yesterday

 

[oliviar]

Apparently, but I don't go out much...

 

[diddi]

ooh thats a bit geeky

 

[inactiveUser333473]

=IF(ISNA(MATCH(A1,$F$1:$F$100,0)),"No","Yes")

fill down

you will need to change the F1:F100 reference to your new sheet

Bumping an old post because this worked for me.

I edited the result to <bold> =IF(ISNA(MATCH(E2,E:E,0)),"Not Found","Value found on row " & MATCH(E2,E:E,0)) </bold>to match my range

I then added traditional CF (I'm using Excel 2003) to E:E column with FormulaIs =E3=E4 and chose a light yellow for the cell colour; which highlights any duplicates of the above rows.


Many thanks, your gift keeps on giving...