VLookup Formula Difficulties

[Mitubulars]

I am using Windows XP Prof V2002 SP3, Excel 2007
This is my first time using mrexcel, so hopefully I provide enough clear information to enable you to assist me.

I start with a list of products on hand. Using this list I need to find and add two additional fields of data. 1-the date the product was first received into stock, and 2-the purchase price.


Example - On Hand Data
1. Purchase Order:CPO3581 (A3), Item Number:I02780642492 (D3), Date (E3), Cost (L3)
2. Purchase Order:CPO3581 (A4), Item Number:I03120920585 (D4), Date (E4), Cost (L4)

Example - Lookup Data
1. Purchase Order:CPO3581 (A2), Date:19/02/2010 (B2), Cost: 278.20 (C2), Item Number:I02780642492 (D2)
2. Purchase Order:CPO3581 (A3), Date:20/03/2010 (B3), Cost: 390.06 (C3), Item Number:I03120920585 (D3)

Current Formula for Item 1 of On Hand Data:
Date: =IF(VLOOKUP(A3,Lookup!$A$2:$D$5,4,FALSE)=D3,VLOOKUP(A3,Lookup!$A$2:$D$3,2,FALSE))
This returns the date of: 19/2/2010 = correct

Current Formula for Item 1 of On Hand Data:
Cost: =IF(VLOOKUP(A3,Lookup!$A$2:$D$5,4,FALSE)=D3,VLOOKUP(A3,Lookup!$A$2:$D$5,3,FALSE))
This returns the cost of: 278.20 = correct

These 2 formulas work if there is only 1 item number possible for each purchase order. In the 2nd example of On Hand data the formula's return both date and cost fields as 'FALSE' which alerts me to manually complete these fields.

My question to this forum is: When I have 1 purchase order, but more than 1 item number, each of which will have 'their own date and cost, is it possible to have the formula look at the purchase order number on hand, search for it in the lookup data, and match the item number to return the correct date and cost. Currently the formula finds the purchase order, looks at the item number, if it is the same as that on hand, it returns the correct date and cost. This item number is the first instance of the purchase order and item. The formula doesn't then continue searching for a second or third instance of the purchase order to match the item number to enable it to return the correct date and cost.

Any suggestions?

 

[Marcelo Branco]

Hi,

Maybe this

Array-formula in E3 (Date)
=INDEX(Lookup!$B$2:$B$100,MATCH(1,IF(TRIM(Lookup!$A$2:$A$100)=TRIM(A3),IF(TRIM(Lookup!$D$2:$D$100)=TRIM(D3),1)),0))

confirm with Ctrl+Shift+Enter
(hold down both Ctrl and Shift kesy and hit Enter)

copy down

Array-formula in L3 (Cost)
=INDEX(Lookup!$C$2:$C$100,MATCH(1,IF(TRIM(Lookup!$A$2:$A$100)=TRIM(A3),IF(TRIM(Lookup!$D$2:$D$100)=TRIM(D3),1)),0))

confirm with Ctrl+Shift+Enter
(hold down both Ctrl and Shift kesy and hit Enter)


copy down

HTH

M.

 

[Mitubulars]

Hi,

Thanks very much. I have followed your instructions in my sample data and it has worked perfectly! Will now add to my working spreadsheet and see how I go.

Rgds
Mito

 

[Marcelo Branco]

Hi,

Thanks very much. I have followed your instructions in my sample data and it has worked perfectly! Will now add to my working spreadsheet and see how I go.

Rgds
Mito

You are welcome and tks for the feedback

M.