Searching in Excel

June 3, 2004 3:17 AM

ExcelFilter: I want to determine if a given value is in a range of cells. A simple true/false evaluation would be sufficient, though returning a reference to the cell might be nice.
posted by namespan to Computers & Internet (4 answers total)

COUNTIF will tell you how many times an item appeared in a range.

Therefore you can stick an IF around this and get:

=IF(COUNTIF([your range],[your value]) > 0, "yes in range", "no is not")

One example would be:

[your range] could be A1:A77
[your value] could be 13

in which case it would show you whether or not the value 13 appeared in the range A1:A77.
posted by ralawrence at 3:44 AM on June 3, 2004

Returning a reference to the cell is going to need a bit of VBA I think.
posted by ralawrence at 3:46 AM on June 3, 2004

Best answer: MATCH returns a reference, so it might do exactly what you want, but I think it only works for a single row or column, not an arbitrary range of cells.
posted by chrismear at 6:00 AM on June 3, 2004

Response by poster: MATCH is very nearly what I'm looking for, and a single row or column will probably work for this case -- thanks chrismear! An arbitrary two-dimensional tool would be nice to have around, though.
posted by namespan at 10:21 AM on June 3, 2004