Join 3,512 readers in helping fund MetaFilter (Hide)


Searching in Excel
June 3, 2004 3:17 AM   Subscribe

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) 1 user marked this as a favorite
 
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


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


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


« Older Fresh juice overload? I just b...   |  I broke Photoshop! [color-rela... Newer »
This thread is closed to new comments.