Forum Discussion

Houston-Jack's avatar
Houston-Jack
Copper Contributor
Apr 16, 2020
Solved

How can I match two columns in a table to pull from a corresponding 3rd column?

I have a table of rows sorted by "project" where a Project can have multiple solution components represented in separate rows, each with a resource assigned.  As in: For example: I need to fin...
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Apr 17, 2020

    Houston-Jack Replicated your table and used the new FILTER function to find the Resource based on the combination of Project and Solution. If your Excel version does not recognise it, a more traditional approach need to be taken.

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Apr 17, 2020

Houston-Jack Replicated your table and used the new FILTER function to find the Resource based on the combination of Project and Solution. If your Excel version does not recognise it, a more traditional approach need to be taken.

 

  • Houston-Jack's avatar
    Houston-Jack
    Copper Contributor
    Apr 20, 2020

    Thanks for the elegant solution, Riny_van_Eekelen, I was a bit hung up on the AND of the two logical expressions.  Your solution also provided a means of finding all the related matches and not stopping on just the first result.  I was able to use TEXTJOIN to assemble the results in a single cell.  My final formula looked like this:

     

    =TEXTJOIN(" ",TRUE,FILTER('Estimate Data'!$D:$D,('Estimate Data'!$I:$I=$B3)*('Estimate Data'!$K:$K=Sheet1!C$2),""))

Resources