CODEWITHSUNDEEP
excelexcel-formuladaxexcel-2019
Istiak Mahmood
Istiak Mahmood

Reputation: 2422

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!

Upvotes: 0

Views: 1043

Answers (2)

Variatus
Variatus

Reputation: 14383

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.

Upvotes: 1

MacroMarc
MacroMarc

Reputation: 3324

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

Upvotes: 1

Related Questions