lookup in multiple columns

[Robert]

Using this list:

100 200
101 102
201 202

I want to check whether e.g. 201 is present in this table.

Using a MATCH function I can lookup in one column at the time but as
201 can be in either column I don't want multiple MATCH functions.

=MATCH(201;A1:A3;0) for column A
or
=MATCH(201;B1:B3;0) for column B

Using something like a nested IF might work but is quite complicated:
=IF(ISERROR(MATCH....

Any simple solutions?

Thanks - Rob

 

[Geoff Lilley]

I can't think of any way around doing this:
=IF(AND(ISNA(MATCH(201,A1:A3,FALSE)),ISNA(MATCH(201,B1:B3,FALSE))),"Not
Found","Found")

The AND is because MATCH only works on a single-column or single-row
array of data. So, the function first checks to see if the number 201
can be found in the range A1:A3. If not, then check to see if it's
foundin B1:B3. If it is not found in either, return "not found." If
it is found in one or the other, then return "Found."

HTH.
Cheers,
Geoff Lilley
Microsoft Office Master Instructor (2000/XP)
Apple HelpDesk Specialist

 

[Geoff Lilley]

I stand corrected. Enter the following formula:

=OR(A1:A3=B1:B3)

Then hit CTRL+U, then CMD+SHIFT+ENTER (CMD means "apple.")

That'll return either "TRUE" or "FALSE."

=IF(OR(A1:A3=B1:B3),"Found","Not Found")

Again, hit CTRL+U (edit the formula), then hit CMD+SHIFT+ENTER to make
it an array formula.

Don't know if that's any less complicated. But it's a good formula.
Cheers,
Geoff

 

[Bob Greenblatt]

Using this list:

100 200
101 102
201 202

I want to check whether e.g. 201 is present in this table.

Using a MATCH function I can lookup in one column at the time but as
201 can be in either column I don't want multiple MATCH functions.

=MATCH(201;A1:A3;0) for column A
or
=MATCH(201;B1:B3;0) for column B

Using something like a nested IF might work but is quite complicated:
=IF(ISERROR(MATCH....

Any simple solutions?

Thanks - Rob

A very easy way is to use an array formula. Assume your values are in A1:B3,
then the formula =or(201=a1:b3) will work fine. Type the formula in a cell
and then press Control-Enter to array enter it. If properly understood by
Excel, it will place braces around the formula to show that it is array
entered.