Pulling a cell value from a specific sheet (in same file) based on multiple criteria - I'm lost...

[eleine]

Hello

Newbie at work, so please be gentle...

(have searched the forum for similar problems but alas... if I have missed a crucial post, please redirect as appropriate)

Excel 2010 32bit, Win 7 64bit

I have a relatively complex spreadsheet made for calculation of labour- and labour-related costs for a ship's crew (I work in a shipping company).
In order to improve the "usability" I am looking to automate how a certain cell is populated

The workbook consist of a "calculation sheet" and several sheets containing variable information (wage matrixes)
Based on type of vessel (set in a separate cell) I'm trying to "pull" the correct wage from the respective sheet based on a cross between type of position, type of agreement and applicable seniority.

Workflow: I select vessel class (dropdown from set cell range), select position (dropdown from set cell range), type of agreement (dropdown from set cell range), level of seniority (dropdown from set cell range) => cell for dayrate populated with the correct value coming from the sheet matching the 4 mentioned criteria:
Vessel class: variable from list (2 choices as of today, could be expanded)
Position: variable from list
Type agreement check: true/false
Seniority: variable from list

I have put up a clean version of the spreadsheet for review here: https://dl.dropboxusercontent.com/u/11001765/Vessel Crew Budget Analysis - DRAFT10.xlsm
the "important cells" have comments in them (C8, B11, F11, H11)

I have been playing with several types of IF/nested-IF's but I cannot wrap my brain around it....
If it is eaiser to have all the positions/wages in one list this is not a problem, current setup is just a remake of how the information is exported from our payroll system.

I am not restricted to the format on the wage matrixes and can uses fewer/several sheets if that makes this more manageble, open to all ideas!

Very appreciative for any help/input you can offer, thanks!

Best regards
Endre

 

[Solosideshow]

i'm having a hard time figuring out what you need done..

 

[eleine]

Re:[SOLVED] - Pulling a cell value from a specific sheet (in same file) based on multiple criteria - I'm lost...

Hello again,

Problem solved!

Feeling a bit embarrased, a few more hours of googling on this forum and various blog posts, I discovered that I was looking for a 2-dimensional lookup...

Playing around with various versions I ended up with this formula that solves my problem:
=IF(F12="NOR";(IF($C$8=4;VLOOKUP(B12;CL4_MATRIX;(SUM(1;(MATCH(H12;SEN_CL_4;0))));FALSE);(IF($C$8=5;(VLOOKUP(B12;CL5_MATRIX;(SUM(1;(MATCH(H12;SEN_CL_5;0))));FALSE));0)))/30)*(1+$C$9);0)

For you Excel wizards out there this may be crude and "low-tech", but is does the job. Checks one main yes/no criteria, and returns the corresponding wage based on selected Position and Seniority (stored in 2 separate sheets, same workbook).
I have posted a version of the final file here (dummy figures) so if anyone wants to take a look I'm all open for improvements

https://dl.dropboxusercontent.com/u/11001765/Vessel Crew Budget Analysis - DRAFT11.xlsm

Sorry to seize your time and @Solosideshow: thanks for you interest to assist!

Brgds
Endre