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.
[TABLE="width: 578"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 2"]Lookup criteria[/TD]
[TD]Return[/TD]
[TD][/TD]
[TD="colspan: 3"]Data Match Table[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AAA[/TD]
[TD="align: right"]111[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD]AAA[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]222[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AAA[/TD]
[TD="align: right"]222[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD]BBB[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]444[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DDD[/TD]
[TD="align: right"]777[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD]CCC[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]666[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CCC[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]DDD[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]111[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BBB[/TD]
[TD="align: right"]333[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AAA[/TD]
[TD="align: right"]222[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]DDD[/TD]
[TD="align: right"]111[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]CCC[/TD]
[TD="align: right"]555[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]AAA[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.