Search code examples
excelexcel-formuladaxexcel-2019

Excel If Condition matches in two column fetch value

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

enter image description here

Can anyone help me to solve this problem!

Solution

  • 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)); ""); "")