VlookupMatchMatch/IndexMatchMatch help

[rugbytomc]

Hi
I'm trying to look from one table to another, to see if a match is found (easy enough with vlookup) but i want to match on Code first, then on a specific requirement and that requirement could be found in any one of 11 columns in one row and i'm struggling to do this so any help much appreciated. Table 1 is the starting point and is populated. Table 2 left hand side is already populated also. Table 2 right hand side is where i want to look for cell B2 (Type aaaa for Code A111) in table 1 and see if it exists (which is does in cell B2)

Table 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Type 1[/TD]
[TD]Type 2[/TD]
[TD]Type 3[/TD]
[TD]Type 4[/TD]
[TD]Type 5[/TD]
[TD]Type 6[/TD]
[TD]Type 7[/TD]
[TD]Type 8[/TD]
[TD]Type 9[/TD]
[TD]Type 10[/TD]
[TD]Type 11[/TD]
[/TR]
[TR]
[TD]A111[/TD]
[TD]aaaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A112[/TD]
[TD]aaaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A113[/TD]
[TD]aaaa[/TD]
[TD]bbbb[/TD]
[TD][/TD]
[TD][/TD]
[TD]dddd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A114[/TD]
[TD]dddd[/TD]
[TD]aaaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]eeee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Type 1 Requirement[/TD]
[TD]Type 2 Requirement[/TD]
[TD]Type 3 requirement[/TD]
[TD]blank[/TD]
[TD]Type 1 Available[/TD]
[TD]Type 2 Available[/TD]
[TD]Type 3 Available[/TD]
[/TR]
[TR]
[TD]A111[/TD]
[TD]aaaa[/TD]
[TD]bbbb[/TD]
[TD]cccc[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A112[/TD]
[TD]dddd[/TD]
[TD]eeee[/TD]
[TD]ffff[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A113[/TD]
[TD]dddd[/TD]
[TD]eeee[/TD]
[TD]ffff[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A114[/TD]
[TD]aaaa[/TD]
[TD]bbbb[/TD]
[TD]cccc[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

If ive read correctly try this. Put you table 1 in A1:L5. Table 2 in A8:H12. In F9 place this:

=IF(COUNTIF(INDEX($B$1:$L$5,MATCH($A9,$A$1:$A$5,0),0),B9),"Yes","No")

Copy across and down.

 

[rugbytomc]

Thanks Steve that's really helpful. Slight tweak - what if the "code" appears more than once in Table 1, is it possible for table 2 to search every line that the code appears to search for the criteria?