Match criteria against multiple cells without OR function

[ExcelJournyman]

So here is the deal. I want a function in column C that will use Column A (as A1, A2, etc) as criteria. I want to search in the Data Match Table for row AAA, BBB, etc. and return True if the 2nd criteria in column B matches any of the values for that given row in Column F or G. I don't want to use OR functions because I want to be able to add new columns to search against without having to mess with the function too much.
Please let me know if you need anything cleared up or have a solution.

	A	B	C	D	E	F	G
1	Lookup criteria	Return		Data Match Table
2	AAA	111	TRUE		AAA	111	222
3	AAA	222	TRUE		BBB	333	444
4	DDD	777	TRUE		CCC	555	666
5	CCC	0	FALSE		DDD	777	111
6	BBB	333	TRUE
7	AAA	222	TRUE
8	DDD	111	TRUE
9	CCC	555	TRUE
10	AAA	0	FALSE

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Is the last record of the table correct? That is, is it DDD,777,111?

 

[ExcelJournyman]

Yes. This just an example list of values of course. Basically, the values in Column F or G aren't unique. Otherwise, I would just use a 2 column VLOOKUP.

 

[Marcelo Branco]

Maybe...

C2 copied down
=ISNUMBER(MATCH(B2,INDEX(F$2:G$5,MATCH(A2,E$2:E$5,0),0),0))

M.

 

[Aladin Akyurek]

Yes. This just an example list of values of course. Basically, the values in Column F or G aren't unique. Otherwise, I would just use a 2 column VLOOKUP.

The way the majority of records of your table are structured, i.e. F < G, made me to ask the question. Since that is not the case:

=ISNUMBER(MATCH(B2,INDEX($E$2:$G$5,MATCH(A2,$E$2:$E$5,0),0),0))

will suffice, as Marcelo also concludes.

 

[ExcelJournyman]

Oh my! Thank you! I had not conceived of another solution besides trying to compare column B directly against the returned contents of columns F and G.