Finding a value by searching multiple columns

[Texas Longhorn]

This should be easy, but even after searching the board, I can't figure it out. I someone could offer the solution or post a link to it, that would be great. Here's what I've got: In Sheet1, I have one column of user id's column A, A1:A200. On Sheet2, I have three columns, Director, Manager, and Rep (A1:A100, B1:B100, and C1:C100, respectively). In column B on Sheet1, I want to lookup the value in column A and see if it is in column A, B, or C on Sheet2.

I don't need the formula to tell me which of the 3 columns it exists in (though that would also be cool). I just need to know if it's in any of the three columns, yes or no.

Many thanks!

 

[Texas Longhorn]

Probably not the most efficient method, but for those interested:

In Sheet2, I concatenated A, B, and C in column D via =a1&b1&c1. Then, in column B of Sheet1 I entered this formula:

=VLOOKUP("*"&A2&"*",Sheet2!$D$2:$D$100,1,FALSE)

It worked well enough for my purposes.

Thanks,

Bill

 

[Russell Hauf]

This should also work:

=COUNTIF(Sheet2!A1:C100,A1)

Will be 0 if no match, and > 0 if there is a match. Or, if you want TRUE/FALSE, you could try:

=COUNTIF(Sheet2!A1:C100,A1)>0

Hope this helps,

Russell

 

[Aladin Akyurek]

Book6
	A	B	C	D	E
1		1	2	3
2	09E	0	0	0
3	05E	0	0	1
4	10B	0	0	0
5	06D	0	0	1
6	01E	0	0	0
7	02C	0	0	0
8	02A	0	0	0
9	02D	0	0	0
10	06E	1	0	0
Sheet1

In B2 enter & copy across then down:

=--ISNUMBER(MATCH($A2,INDEX(Sheet2!$A$2:$C$10,0,B$1),0))

 

[Texas Longhorn]

Thanks to you both. Aladin, my data is all text. Does this affect your solution? I'm returning all zeros. I know I've read on here about the "--", but I need to check again on what it does.

Thanks

 

[Aladin Akyurek]

Thanks to you both. Aladin, my data is all text. Does this affect your solution? I'm returning all zeros. I know I've read on here about the "--", but I need to check again on what it does.

Thanks

MATCH can take most values (logicals,numbers,dates,times, and text) as lookup values. Incidentally, my exhibit consists of text values.