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
Last edited by ExGrunt; 23rd Jul 2014 at 09:53.