Vlookup and check a condition in Excel-CodePudding

I have two tables:

Table 1: includes Name and Grade Table 2: includes Name and Gender

I need to do vlookup for names in Table 1 to see if they exist in Table 2and check the gender.

So, if vlookup finds a match and gender="M", then result=Pass, else fail.

Here are Tables 1 and 2 with column names:

A   B
Name Grade
adam 14
nancy 11
rob 33
jon 33
sara 111
sharon 55
james 66

G        H
Name    Gender
james    M
bill    M
mason    M
sarah    F
sara    F
adam     M

Here is the final table with the "result" column:

A    B     C
Name Grade Result
adam 14    Pass
nancy 11   Fail 
rob 33     Fail
jon 33     Fail
sara 111   Fail 
sharon 55  Fail
james 66   Pass

I can do the vlookup to find the match using (for the first record):

=IF(ISERROR(VLOOKUP(A2,$G$2:$G$7,1,0)),0,1)

But how can i also check for gender?

CodePudding user response：

If names are unique, use:

=INDEX({"Fail","Pass"},COUNTIFS(G:G,K2,H:H,"M") 1)

if not:

=INDEX({"Fail","Pass"},(COUNTIFS(G:G,K2,H:H,"M")>0) 1)