Sep 15 2020 07:12 AM
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 ,
Sep 15 2020 08:19 AM
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.
Sep 15 2020 09:52 AM
Sep 15 2020 10:35 AM
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.
Sep 15 2020 09:34 PM
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.