Formula help

[Bellablu]

Hi,

I am trying to write a formula to give me one piece of info from one sheet into another with multiple criteria’s. I have tried doing a Match, IF & Lookup formula but I am not having any success. I need some help from someone with a little more expertise.

I am trying to write a formula that will give me the value from sheet 1 column A in Sheet 2 Column E & G respectively. The criteria would be the Cost Centre Code (Sheet 1 Column E / Sheet 2 Column B) & the Amount (Sheet 1 Column J / Sheet 2 Column D & F respectively. Hope this makes sense.

Any guidance will be greatly appreciated.</SPAN>
Sheet 1

<o> </o>​	A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​	F<o></o>​	G<o></o>​	H<o></o>​	I<o></o>​	J<o></o>​
1<o></o>​	Billing Period<o></o>​	Invoice Dates<o></o>​	Service<o></o>​	Account ID<o></o>​	Cost Centre Code<o></o>​	STD Class Code<o></o>​	Std Class Description<o></o>​	Journal Date<o></o>​	Description<o></o>​	Amount<o></o>​
2<o></o>​	Mar'14 - May'14<o></o>	6 Mar - 16 Jun 214<o></o>	Water<o></o>	79CC04W324111<o></o>	79CC04W<o></o>	324111<o></o>	Water And Sewerage<o></o>	7 Jul 14<o></o>​	Lot 1<o></o>	192.22<o></o>​
3<o></o>​	Apr'14 - Jun'14<o></o>	1 Apr - 30 Jun 14<o></o>	Sewerage<o></o>	79CC04W324111<o></o>	79CC04W<o></o>	324111<o></o>	Water And Sewerage<o></o>	7 Jul 14<o></o>​	Lot 2<o></o>	190.94<o></o>​
4<o></o>​	Jun'14<o></o>	21 May - 20 Jun 14<o></o>	Water<o></o>	79PC01A324111<o></o>	79PC01A<o></o>	324111<o></o>	Water And Sewerage<o></o>	8 Jul 14<o></o>​	Lot 3<o></o>	784.13<o></o>​
5<o></o>​	Jun'14<o></o>	21 May - 20 Jun 14<o></o>	Water<o></o>	79PC01A324111<o></o>	79PC01A<o></o>	324111<o></o>	Water And Sewerage<o></o>	8 Jul 14<o></o>​	Lot 4<o></o>	17,115.26<o></o>​
6<o></o>​	May'14<o></o>	23 Apr - 20 May 14<o></o>	Water<o></o>	79PC01A324111<o></o>	79PC01A<o></o>	324111<o></o>	Water And Sewerage<o></o>	11 Jul 14<o></o>​	Lot 5<o></o>	-643.15<o></o>​
7<o></o>​	Jun'14<o></o>	21 May - 20 Jun 14<o></o>	Water<o></o>	79PC01A324111<o></o>	79PC01A<o></o>	324111<o></o>	Water And Sewerage<o></o>	11 Jul 14<o></o>​	Lot 6<o></o>	-784.13<o></o>​
8<o></o>​	Jun'14<o></o>	15 May - 17 Jun 14<o></o>	Water<o></o>	79PW01T324111<o></o>	79PW01T<o></o>	324111<o></o>	Water And Sewerage<o></o>	8 Jul 14<o></o>​	Lot 7<o></o>	3,885.30<o></o>​
9<o></o>​	Jun'14<o></o>	24 May - 23 Jun 14<o></o>	Water<o></o>	79QD02D324111<o></o>	79QD02D<o></o>	324111<o></o>	Water And Sewerage<o></o>	11 Jul 14<o></o>​	Lot 8<o></o>	3,921.73<o></o>​

<TBODY> </TBODY>

Sheet 2

<o> </o>​	A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​	F<o></o>​	G<o></o>​
1<o></o>​	Cost Centre<o></o>​	Cost Centre 2014-2015<o></o>​	Address<o></o>​	Period 1 Charges<o></o>​	Billing Period<o></o>​	Period 2 Charges<o></o>​	Billing Period<o></o>​
2<o></o>​	79CC04T<o></o>	79CC04T<o></o>	Lot 1<o></o>	0.00 <o></o>​	<o></o>	0.00 <o></o>​	<o></o>
3<o></o>​	79CC04W<o></o>	79CC04W<o></o>	Lot 2<o></o>	0.00 <o></o>​	<o></o>	0.00 <o></o>​	<o></o>
4<o></o>​	79PB01A<o></o>	79PC01A<o></o>	Lot 3<o></o>	192.22 <o></o>​	<o></o>	0.00 <o></o>​	<o></o>
5<o></o>​	79PB01D<o></o>	79PC01D<o></o>	Lot 4<o></o>	16,472.11 <o></o>​	<o></o>	11,592.95 <o></o>​	<o></o>
6<o></o>​	79PW01T<o></o>	79PW01T<o></o>	Lot 5<o></o>	0.00 <o></o>​	<o></o>	9,360.89 <o></o>​	<o></o>
7<o></o>​	79QD02D<o></o>	79QD02D<o></o>	Lot 6<o></o>	3,885.30 <o></o>​	<o></o>	2,998.10 <o></o>​	<o></o>
8<o></o>​	79QD03A<o></o>	79QD03A<o></o>	Lot 7<o></o>	3,921.73 <o></o>​	<o></o>	3,549.12 <o></o>​	<o></o>
9<o></o>​				1,508.40 <o></o>​	<o></o>	1,088.82 <o></o>​	<o></o>
10<o></o>​		79PJ23A<o></o>		0.00 <o></o>​	<o></o>	0.00 <o></o>​	<o></o>
11<o></o>​				25,979.76 <o></o>​	<o></o>	28,589.88 <o></o>​	<o></o>

<TBODY>
</TBODY>

 

[Canapone]

Hi,

an attempt, I cannot check numbers in column J sheet1 and column B sheet2: there could be hidden decimals.

=IFERROR(INDEX(SHEET1!$A$2:$A$100,MATCH($B2&D2,INDEX(SHEET1!$E$2:$E$100&SHEET1!$J$2:$J$100,0),0)),"")

Just for sharing a first formula.

Cheers

 

[hohlick]

I'm unable to understand, which 2nd billing period and amount correspond to Lot 1 - there are only one row with Lot1 in source table (so only one billing period and charge amount), and second row with the same cost centre name has Lot 2...