IF formula won’t work right (Excel 2000 SR1)

Topic

New Reply

I’m comparing Column C to Column A – column A has ALL valid values and I want to weed out the entries in Column C that are no valid, ie. do not occur in Column A. I am using this IF statement:

=IF(C2=$A$2:$A$1217,”VALID”,”delete”)

but it only returns a TRUE value when the values in Col. C and Col. A appear in the same row.
One value appears in C106 and A111, but since they are not on the same row the formula returns a FALSE value even though it is obviously in both columns.

How can I change this so I get a TRUE response if the cell value in Col. C appears ANYWHERE in Col. A, not just in the same row?

Many thanks –

Reply | Quote

Replies

=IF(ISERROR(VLOOKUP(C2,$A$2:$A$1217,1,FALSE)),”delete”,”VALID”)

Reply | Quote

Hi,
In addition to John’s formula, you could also use:
=OR(C2=$A$2:$A$1217)
array-entered (i.e. press Ctrl-Shift-Enter)
Hope that helps.

Reply | Quote

=IF(COUNTIF($A$2:$A$1217,C2),”VALID”,”delete”)

should be a bit more efficient.

Reply | Quote