An Excel question
 

The project in hand is a Excel workbook which will handle the allocation of 'jobs completed per month bonus kitty' and the allocation of such bonuses to qualifying staff members in any given month.

'Worksheet1' primarily calculates the bonus kitty amount earned for a specific number of completed jobs in any month.

There are other calculations there (minimum bonus guarantees, total earnings, projected annual earnings etc) but these do not affect the core question.

The core data lines are:

1) Job number (1 - 40)
2) Amount allocated to 'the kitty' per job (jobs 1 - 6 earn £22 each, jobs 7 - 15 earn £25 each, jobs 16 - 25 earn £30 each etc etc)

and from this a simple calculation of:

3) Total amount allocated to allocated to 'the kitty' in a given month based on qualifying jobs completed


'Monthly worksheets' count the qualifying jobs completed - and will subsequently share out the bonus kitty.

Again there are some other calculations subsequently (monthly staff costs, actual revenue, operating profit/loss etc) but again these do not affect the core question.


What I want to do is use the 'value' in the box showing qualifying jobs in the month (a number between 1 and 40) to 'lookup' the corresponding 'kitty value' in 'worksheet 1' and insert it in another cell in the monthly worksheet.

i.e
qualifying jobs counted = 16 (calculated on 'monthly worksheet')
16 jobs completed = £426 bonus (already calculated and displayed on 'worksheet 1
insert £426 in appropriate cell on 'monthly worksheet'


As I understand it, 'if functions' can't cope with this because of the number of options (1 - 40, in practice I could probably do away with 1 - 5 but this doesn't make a difference) but will be delighted if someone tells me otherwise.

I have looked at 'lookup' (in its various versions: VLOOKUP etc) and 'pivot tables' (neither of which are my areas of expertise admittedly) but can't see a way to do this.


If someone could just point me in the right direction I have a coffee table full of manuals to work through the detail - thanks in advance

I think VLOOKUP is exactly what you want. As I am chained to my desk and have nothing better to do, I tried to interpret your post into an example:

http://i.imgur.com/S7f6pUF.png

In this case the formula returns 507 quid. Correct?

looks good - am off to play - back in 10 minutes

Solved ...
 

Yup - switch to 'H' (rather than 'V') in my case, remove gaps between data rows, insert 'other sheet pointer' and Bob's yer Aunty's Uncle !!


Knew it had to be there somewhere - many thanks Booglebox

SPM,

[Pedant mode: on]
Strictly, HLOOKUP and VLOOKUP are legacy functions and their use is discouraged in spreadsheets where rows of data are being inserted and deleted. This is because they tend to be a bit 'brittle' when dealing with changing ranges and it is often not obvious that an error has crept in. The preferred functions are a combination of INDEX and MATCH (or MMULT or SUMPRODUCT).

eg: INDEX( Row Range I want a return value from , MATCH ( Lookup Value , Row Range I want to check against , 0 For exact match), Column offset)

So using Booglebox's example table the function would be:
=INDEX(D3:D42, MATCH(H2,B3:B42,0),0)

[Pedant mode:off]

But, if HLOOKUP works for you then don't fix what ain't broke.

However, I would strongly urge that you perform check calculations on anything that involves money. This is a completely separate calculation that shows there is no mechanical error in the spreadsheet. A possible example is the reverse look up eg lookup the kitty value and see that it matches the correct number of completed jobs.

Further, if you are doing cross sheet lookups I would recommend using named ranges. So select B3:B42 on sheet 1 and key ALT+I, N , D and enter 'Jobs'. Then select D3:D42 and use the same key sequence and enter 'Cumulative'. Then the formula on every sheet becomes:

=INDEX(Cumulative, MATCH(H2,Jobs,0),0)

HTH

EG

That's not the right attitude !

If you know there are likely to be issues down the line (as you outlined yourself), then better to swat the bug now rather than having to waste time finding the bug later and then fixing it.

Of course really he should be using a database rather than abusing an Excel spreadsheet as I've got doubts about his scalability... but that's another story ;)

That is very true. Index and Match are far more robust, and also more flexible, but also a bit more confusing.