If - and - vlookup

[jwang776]

Hi Everyone,

I have a table where I'm trying to lookup a transaction ID for a particular sales member. I'm not sure how to approach this and my efforts around an IF( AND( VLOOKUP( formula have proved futile.

The following tables show what I'm trying to accomplish.


TABLE 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Associate (A)[/TD]
[TD]Transaction ID (B)[/TD]
[/TR]
[TR]
[TD]Jon Snow[/TD]
[TD]??? - Trying to find the transaction ID for a Dragon Sword sold by Jon Snow[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Associate[/TD]
[TD]Transaction ID[/TD]
[TD]Item Class[/TD]
[TD]Item Type[/TD]
[/TR]
[TR]
[TD]Jon Snow[/TD]
[TD]45A[/TD]
[TD]White Walker[/TD]
[TD]Sword[/TD]
[/TR]
[TR]
[TD]Tyrion Lannister[/TD]
[TD]4234236A[/TD]
[TD]Giant[/TD]
[TD]Mace[/TD]
[/TR]
[TR]
[TD]Jon Snow[/TD]
[TD]46347A[/TD]
[TD]Dragon[/TD]
[TD]Sword[/TD]
[/TR]
[TR]
[TD]Tyrion Lannister[/TD]
[TD]4423428A[/TD]
[TD]Dragon[/TD]
[TD]Tooth[/TD]
[/TR]
[TR]
[TD]Jon Snow[/TD]
[TD]4121239A[/TD]
[TD]Giant[/TD]
[TD]Club[/TD]
[/TR]
[TR]
[TD]Jon Snow[/TD]
[TD]553450A[/TD]
[TD]Giant[/TD]
[TD]Boot[/TD]
[/TR]
[TR]
[TD]Sansa Stark[/TD]
[TD]341231232A[/TD]
[TD]White Walker[/TD]
[TD]Glove[/TD]
[/TR]
[TR]
[TD]Jon Snow[/TD]
[TD]534534534A[/TD]
[TD]Dragon[/TD]
[TD]Tooth[/TD]
[/TR]
[TR]
[TD]Sansa Stark[/TD]
[TD]123123A[/TD]
[TD]Wight[/TD]
[TD]Cloak[/TD]
[/TR]
[TR]
[TD]Sansa Stark[/TD]
[TD]234590789A[/TD]
[TD]Wight[/TD]
[TD]Sword[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I was thinking IF(AND(VLOOKUP(A2,TABLE2(A1:D11),4,false)="SWORD",VLOOKUP(A2,TABLE2(A1:D11),3,false)="DRAGON"),VLOOKUP(A2,TABLE2(A1:D11),2,false),"THIS FORMULA DID NOT WORK")

This was definitely not the correct logic and any help in understanding this type of query is extremely appreciated! Thank you to everyone for reviewing!

p.s. running excel 2013

Sincerely,

Jon

 

[shaowu459]

Re: Seeking Expert Help If Available, Complex IF - AND - VLOOKUP

=INDEX(B1:B11,MATCH("Jon Snow"&"Dragon"&"Sword",A1:A11&C1:C11&D1:D11,))

array formula, run with Ctrl+shift+enter

 

[jwang776]

Re: Seeking Expert Help If Available, Complex IF - AND - VLOOKUP

=INDEX(B1:B11,MATCH("Jon Snow"&"Dragon"&"Sword",A1:A11&C1:C11&D1:D11,))

array formula, run with Ctrl+shift+enter

Hi Shaowu. This is exactly what I was looking for!! Many thanks and extremely appreciated. Do you recommend a good place for learning typical array formulas? Up until now, I had not explored this topic very much and am eager to learn! Anyhow, thank you for the amazing solution.

-Jon

 

[shaowu459]

Re: Seeking Expert Help If Available, Complex IF - AND - VLOOKUP

Morning, I'm located in china, I learned knowledge about excel in an excel forum, but the language is chinese. Hope you can master array formula soon, you will see a wonderful and amazing world