Compare one cell with a range ....

[nedy_03]

Hello,

How can I compare one cell with a range ?? ... Something like: if A1 value is through b1:b100 values to get an "ok" ...

Thx,
Nedy

[lucas]

Something like: if A1 value is through b1:b100 values to get an "ok" ...

Can you clarify this statement......I don't understand.

[Bob Phillips]

=IF(ISNUMBER(MATCH(A1,B1:B100),0)),"ok","")

perhaps

[nedy_03]

What does that "0" do ?? cause I get an error on it ? ..

Thx,
Nedy

[Zack Barresse]

It is for an exact match. Bob misplaced a paren...

=IF(ISNUMBER(MATCH(A1,B1:B100,0)),"ok","")

HTH

[nedy_03]

I'll try to explain better what I'm looking for .. In the attach file in "C1" I would need a formula that looks through the "B1:B18" values. And if one of the "B1:B18" values match the A1 value, in "C1" I should get on "OK" ...

[Zack Barresse]

Without looking at your file, in C1, enter this formula...

=IF(ISNUMBER(MATCH(A1,B1:B18,0)),"OK","NOT OK")

HTH

[lucas]

Doesn't Zack's correction do exactly that? Post #5

[nedy_03]

AND IF I HAVE TO APPLY THIS FOR TEXT .. CAN I USE "ISTEXT" INSTEAD OF "ISNUMBER" ?? ... I TRIED IT AND IF I USE "ISNUMBER" IT WORKS FOR TEXT TOO ... BUT "ISTEXT" ITDOESN'T WORK ...

THX,
NEDY

[Zack Barresse]

Because ISTEXT() does not see if the MATCH() function found a match or not, as MATCH() returns a numeric position of (with the 0 in the third syntax) the matched value. You cannot switch it out that way. The function works exactly as you requested.

[Bob Phillips]

It is for an exact match. Bob misplaced a paren...

=IF(ISNUMBER(MATCH(A1,B1:B100,0)),"ok","")

HTH

I spotted that and thought I corrected it before I posted