Multiple Match Help

[thussain]

I'm struggling with a formula. I have made a payment calculator (attached) but I'm having issues to which I've found a solution but not sure how to execute it.

Currently the formula in cell C11 offsets and matches data from sheet 2. As of now it is matching based on the month selected from cell c8 however I also need to match the number of weeks in cell c5.

In other words right now it is matching sheet 1 cell C8 with sheet 2 row c13, what I need it to do is match sheet 1 cell C8 with sheet 2 row c13 and sheet 1 c5 with sheet 2 row c12. So when sheet 2 C12 and C13 match sheet 1 C5 and C8 it returns the result as requested. I hope I'm making sense. If you have a look at my calculator it will make more sense I think. Please find it attached and I appreciate if anyone can help me figure this out. It is currently only set up to return results for weeks 35 and 36 (C5). And because I haven't executed what I'm asking, only results for 36 weeks are working correctly.

Calculator Months New.xlsx

[Richard Buttrey]

Hi,

I can't help thinking you would simplify things if you had a more straightforward sheet2 layout. It's not clear, at least to me, what the overall object of the exercise is and how you use it in practice.

If you could describe the task in a narrative form, we can maybe suggest a simpler approach.

[thussain]

Thanks for your reply Richard.

Basically we pay students at College for travel costs. Their application depends on how many weeks they have remaining at College.

Hence the calculator makes it easier for everyone to enter payments in to our payment system. So on this spreadsheet you would enter the basic information requested. e.g.

Days Attends: 4
Weeks at College: 36
Daily Amount £4.60
Annual: (auto-generated) £331.20
Month Start: September

This will now calculate the split for the annual amount in the table below:
September to June and all we have to do is copy it over instead of using a calculator and breaking payments down - it makes life easier for everyone.

This calculation in the Sep-June table is coming from the data contained in Sheet 2 (which is why it's probably a mess)

Row 13 in Sheet 2 contains the months I need it to match from the month selected in Sheet 1 cell C8. I only had one set to begin with in Row 13 i.e. Sep to June so whatever month was selected it would copy the calculations that were done to the right cell of the month from Sheet 2 in to Sheet 1.

However these calculations are only correct I've noticed if the number of weeks the student is with College is 36. If you enter the data I've given at the top or enter any data you like with the week number remaining at 36, the result is correct. If you change the number of weeks to 35 it no longer works - the box at the bottom in red will highlight there is an error.

So I thought if I can change the match to match two criteria it will work i.e. the month and the number of weeks. So in Sheet 2 I have started a new set i.e. one set was already there but it's for 36 weeks so I copy and pasted it all i.e. the Sep-Jun set but on top of each month in Sheet 2 I add the week number. Thus far I've only done it for weeks 36 and 35 (i.e. there are two sets of results Sep-Jun). But I need to adjust the formula in sheet 1 cell C11:20 so it matches the month and also the week number in Sheet 2 and returns the results next to that criteria i.e I need Sheet 1 cell C5 and C8 to match Sheet 2 rows C12 and C13 and then give me the offset.

I don't know if I'm just confusing people more...

P.S. The calculations for the week 35 set may be wrong but I can correct those, the issue is I need the formula in C11:20 to work where it offsets and matches data in Sheet 2 rows C12 and C13... The formula currently offsets and matches just row C13 in Sheet 2.