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
<TBODY> </TBODY>
Sheet 2
<TBODY>
</TBODY>
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>