PDA

View Full Version : Compare one cell with a range ....



nedy_03
01-29-2007, 09:10 AM
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
01-29-2007, 09:29 AM
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
01-29-2007, 09:40 AM
=IF(ISNUMBER(MATCH(A1,B1:B100),0)),"ok","")

perhaps

nedy_03
01-29-2007, 10:44 AM
What does that "0" do ?? cause I get an error on it ? ..

Thx,
Nedy

Zack Barresse
01-29-2007, 10:47 AM
It is for an exact match. Bob misplaced a paren...

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

HTH

nedy_03
01-29-2007, 10:52 AM
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
01-29-2007, 10:59 AM
Without looking at your file, in C1, enter this formula...

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

HTH

lucas
01-29-2007, 10:59 AM
Doesn't Zack's correction do exactly that? Post #5

nedy_03
01-29-2007, 11:07 AM
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
01-29-2007, 11:15 AM
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
01-29-2007, 02:38 PM
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 :doh: