Challenge 7

[Jalal_1988]

Hi dear Friends
I have problem to use VlookUp,
I have two sheet, one list is for purchase and one of them for Selling of our items, 
We have four header fro each items, Item name, Description, Item Cod, Supplier and Purchase Cod, 
Now I want to find the Purchase Price and insert it in Selling list, 
How I can do this ? (in selling list the items are repeated and for your understanding we may have more than one time purchase , the Purchase cod for each item repeated in selling list and if we have two time purchase it mentioned the cods),  (I insert my purchase price manually  in last column of selling list for your understanding)  
In attachments you can find the Excel file, 

Thank you for your helping in advance ,

[#2]

@Jalal_1988 

I think you switched the purchase quantities 20 and 40 in the last 6 rows.

In J6 as an array formula confirmed with Ctrl+Shift+Enter:

=INDEX($T$6:$T$9, MATCH(1, ($Q$6:$Q$9=F6)*($R$6:$R$9=H6), 0))

Fill down.

[#3]

Dear Hans
Thank you for you cooperation,

But please notice that your Formulas is based on purchase quantity which its uncorrected,
Because we have more than 1000 items in our database with more than two time purchase for each items, for example may we have more than many items with 100 PC, (purchase quantity is not unique)

You have two mention formula base on Discretion*, Cod* , Supplier* and Purchase Cod*, two avoid any mistakes,

[#4]

@Jalal_1988 

You will need to create a helper column for this. Concatenate the identifiers from purchase table to get a unique combination. Look it up in the sale table.

See in the attached spreadsheet.

Please note that your data is inconsistent. The description, code and supplier for Item C do not match between two tables. You need to correct the data.

Hope this is helpful! Please vote the post as an answer if this resolves the issue.

[#5]

@Jalal_1988 

How about simply this?

In J6

=INDEX($T$6:$T$9,MATCH(B6&F6,INDEX($M$6:$M$9&$Q$6:$Q$9,),0))

and copy it down.