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

Associate (A)	Transaction ID (B)
Jon Snow	??? - Trying to find the transaction ID for a Dragon Sword sold by Jon Snow

<tbody>
</tbody>

Associate	Transaction ID	Item Class	Item Type
Jon Snow	45A	White Walker	Sword
Tyrion Lannister	4234236A	Giant	Mace
Jon Snow	46347A	Dragon	Sword
Tyrion Lannister	4423428A	Dragon	Tooth
Jon Snow	4121239A	Giant	Club
Jon Snow	553450A	Giant	Boot
Sansa Stark	341231232A	White Walker	Glove
Jon Snow	534534534A	Dragon	Tooth
Sansa Stark	123123A	Wight	Cloak
Sansa Stark	234590789A	Wight	Sword

<tbody>
</tbody>

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