Excel If Condition matches in two column fetch value

Question

I am working on an excel simple query, where I am trying to fetch Name based on

1. first if the ID matches between two tables columns values
2. second if the Status column has a value of Pick, then Fetch the value from the Name column. Else Do nothing and empty

Here are my tables -

Table 2

ID.    Name
1001   Chris
1002   Leoe
1003   Nyle

Table 1

ID.    Status
1001   Pick
1002    No
1003   Pick
1004   Pick

Expected result

ID.    Status.  Name
1001   Pick     Chris
1002    No     
1003   Pick     Nyle
1004   Pick

I am trying this query but something is wrong here -

=IF(ISNUMBER(MATCH([ID];Table2[ID];0));IF([Status]="Pick";Table2[Name];""))

But I am getting this error error

Can anyone help me to solve this problem!

Answer 1

You need to use @ signs to reference the single row of the immediate table. You should also use the Match result again to reference the single row in the second table:

=IF(ISNUMBER(MATCH([@ID]; Table2[ID]; 0)); IF([@Status]="Pick"; INDEX(Table2[Name]; MATCH([@ID]; Table2[ID]; 0)); ""); "")

Answer 2

Presuming that your Table1 and Table2 are Excel tables, you can build the result table as a normal spreadsheet range in A11:C14 with the following formulas.

1. [A11] =Table2[ID.] This will automatically copy itself down to A14
2. [B11] =Table2[Status] This will automatically copy itself down to B14
3. [C11] =IF(B11="Pick",IFERROR(VLOOKUP(A11,Table1,2,FALSE),""),"") Please copy down manually to C14.

The result will be as you describe but not as your sample illustrates because ID 1003 is associated with Nyle, not Leoe.