Excel Matches value and fetch

user_14804525 0
Viewed 32

I am working on an excel simple query, where I am trying to fetch Name based on first if the ID matches, and second if the Status column has a value of Pick, else Nothing

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     Leoe
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!

dax excel-formula excel
1 Answers

If your tables and results are laid as per:

enter image description here

Then, there are two formulas I'd use for

  1. Status =INDEX($D$2:$D$5,MATCH($E2,$C$2:$C$5,0))

  2. Name =IF($F2="No","",IFERROR(INDEX($B$2:$B$4,MATCH($E2,$A$2:$A$4,0)),""))

Note: Name is restricted to the 3 you've identified in table 1. Also, ID 1003 is for Nyle and not Leoe

user_146386820
Related