Help with a potential nested vlookup/hlookup

[Talby99]

Hello Everyone,

I have what I think is a fairly easy problem that I hope I can get some help with.

What I would like to do is have a formula that will help me mark if an individual (distributed by rows [i.e. the names in the table below) have achieved a certain variable (distributed in columns [i.e. the XYZ in the table below) - The formula would look in the array of Table1 (a1:c12) and be entered in the corresponding cells of Table 2.

The Raw data is distributed in a table such as the one below,
Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tim[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Alex[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tim[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Harry[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Harry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Alex[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

I would then like a formula that would tell excel to mark in a separate table (see below) if each individual achieved a certain variable - the formula could either enter a binary value for achieved or not achieved (i.e. 0 or 1 / Yes or No) or enter how many times it has been achieved (so for example Bob has achieved Z twice).

Table 2
[TABLE="width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I thought potential a form of nested vlookup/hlookup may work but am open to anything,

Please feel free to ask any questions that may help you answer my query.

Thank You,

Talby99

 

[Gimics]

Hey Talby99,

Have you tried using a countifs formula? I replicated your Table 1, with the headings in row 1 and the names in column A. In Table 2, I placed the following formula beside Alex and under X, then copied and pasted throughout the table:

=COUNTIFS($A$2:$A$13,$A16,B$2:B$13,"Yes")

"$A16" referred to the cell containing "Alex" in Table 2. This provided accurate results for me.