SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1313820%22%20slang%3D%22en-US%22%3EHow%20can%20I%20match%20two%20columns%20in%20a%20table%20to%20pull%20from%20a%20corresponding%203rd%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313820%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20of%20rows%20sorted%20by%20%22project%22%20where%20a%20Project%20can%20have%20multiple%20solution%20components%20represented%20in%20separate%20rows%2C%20each%20with%20a%20resource%20assigned.%26nbsp%3B%20As%20in%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Houston-Jack_0-1587062569934.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184839iF1C1FB0FFA34864D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Houston-Jack_0-1587062569934.png%22%20alt%3D%22Houston-Jack_0-1587062569934.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFor%20example%3A%20I%20need%20to%20find%20the%20resource%20value%20(%22D%22)%20in%20a%20row%20where%20project%20value%20%3D%202%20and%20Solution%20%3DZ%3C%2FP%3E%3CP%3EA%20LookUp%20on%20Project%20or%20Solution%20will%20not%20isolate%20the%20other%20variable.%26nbsp%3B%20It%20might%20take%20nesting%20of%20the%20LookUps%20that%20I%20haven't%20figured%20out%20yet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1313820%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1314291%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20match%20two%20columns%20in%20a%20table%20to%20pull%20from%20a%20corresponding%203rd%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1314291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625053%22%20target%3D%22_blank%22%3E%40Houston-Jack%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3Dtwo%2Bway%2Blookup%2Bexcel%26amp%3Boq%3Dtwo%2Bway%2Blok%26amp%3Baqs%3Dchrome.2.69i57j0l7.8951j0j7%26amp%3Bsourceid%3Dchrome%26amp%3Bie%3DUTF-8%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETwo%20way%20lookup%20in%20Excel.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1314868%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20match%20two%20columns%20in%20a%20table%20to%20pull%20from%20a%20corresponding%203rd%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1314868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625053%22%20target%3D%22_blank%22%3E%40Houston-Jack%3C%2FA%3E%26nbsp%3BReplicated%20your%20table%20and%20used%20the%20new%20FILTER%20function%20to%20find%20the%20Resource%20based%20on%20the%20combination%20of%20Project%20and%20Solution.%20If%20your%20Excel%20version%20does%20not%20recognise%20it%2C%20a%20more%20traditional%20approach%20need%20to%20be%20taken.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321823%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20match%20two%20columns%20in%20a%20table%20to%20pull%20from%20a%20corresponding%203rd%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321823%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%2C%20but%20my%20need%20was%20to%20match%20on%20values%20in%20two%20columns%20of%20the%20same%20row%20to%20obtain%20the%20value%20in%20a%20third%20column%20of%20that%20row.%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20provided%20an%20elegant%20solution%20to%20that%20in%20the%20post%20below.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321839%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20match%20two%20columns%20in%20a%20table%20to%20pull%20from%20a%20corresponding%203rd%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321839%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20elegant%20solution%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%2C%20I%20was%20a%20bit%20hung%20up%20on%20the%20AND%20of%20the%20two%20logical%20expressions.%26nbsp%3B%20Your%20solution%20also%20provided%20a%20means%20of%20finding%20all%20the%20related%20matches%20and%20not%20stopping%20on%20just%20the%20first%20result.%26nbsp%3B%20I%20was%20able%20to%20use%20TEXTJOIN%20to%20assemble%20the%20results%20in%20a%20single%20cell.%26nbsp%3B%20My%20final%20formula%20looked%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%20%22%2CTRUE%2CFILTER('Estimate%20Data'!%24D%3A%24D%2C('Estimate%20Data'!%24I%3A%24I%3D%24B3)*('Estimate%20Data'!%24K%3A%24K%3DSheet1!C%242)%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321881%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20match%20two%20columns%20in%20a%20table%20to%20pull%20from%20a%20corresponding%203rd%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625053%22%20target%3D%22_blank%22%3E%40Houston-Jack%3C%2FA%3E%26nbsp%3BVery%20good!%20Glad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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:

Houston-Jack_0-1587062569934.png

For example: I need to find the resource value ("D") in a row where project value = 2 and Solution =Z

A LookUp on Project or Solution will not isolate the other variable.  It might take nesting of the LookUps that I haven't figured out yet.

 

Thanks

5 Replies
Highlighted
Best Response confirmed by Houston-Jack (New Contributor)
Solution

@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.

 

Highlighted

 

 

Thanks for the reply, @Detlef Lewin , but my need was to match on values in two columns of the same row to obtain the value in a third column of that row.  @Riny_van_Eekelen  provided an elegant solution to that in the post below.

Highlighted

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

Highlighted