Many Condition need to match and return a unit price - Urgent assignment

Hi All,

I had two spreadsheet here

Sheet 1
A B C D E F
Date Supplier Name PO No. Description of Goods Invoice No. Quantity
2011.1.23


Sheet 2
A B C D E F G
Date Supplier Name PO No. Description of Goods Invoice No. Quantity Unit Price

2011.1.28

Sheet 2 are just have one more column = unit price.

I need to find the unit price from sheet 2 and they are few thousand of transaction in a year in sheet 2. And sheet 2 are a lot of repeated purchases of same commodity but different unit price, different quantity, or PO number in a year.
Therefore i need to do a lookup or match formulae.

First condition= For eg. If sheet 1 date is on 2011.1.13, but sheet 2 transaction enter date is on 2011.1.28 (which based on delivery date). The price that i look from sheet 2 must be within the month of January 2011. The transaction date in sheet 2 is not allowed to enter into system after January but can be anyday in January.

Second = if above first condition match, then both sheet 1 & 2 Supplier name, PO number, description and invoice no, quanity need to match.

Then the sheet 1 unit price can come

THanks all..

Really appreciate!!!!

Hi,

in this example you get the first price in column G of sheet2 (g2:g1000) when 3 conditions are met

(a2-b2-b2 sheet1)

=MONTH(A2)=MONTH(Sheet2!A2:A1000)

=B2=Sheet2!B2:B1000

=C2=Sheet2!C2:C1000


=INDEX(Sheet2!G2:G1000,MATCH(MONTH(A2)&B2&C2,INDEX(MONTH(Sheet2!A2:A1000)&Sheet2!B2:B1000&Sheet2!C2:C1000,0),0))


You can simply add any other corrispondence you need.

Better if you can attach a sample file in order to adjust the formula.

Hope it helps

What does it means a2-b2-b2 sheet 1?

=month(a2).. really cannot understand. Perhaps you are right . i should attached it.

Thank you...