Forum Discussion
VLOOKUP help required
I am not sure whether to use nested VLOOKUPs or MATCH/INDEX and VLOOKUP to implement what I’m trying to do.
On the main worksheet I have populated the Project Category using VLOOKUP based on the project lifecycle entered by the user. =VLOOKUP(H4,Category,2,FALSE) – this works fine and is using Lookup table 1.
On the main worksheet, the user will enter in a score for each of the Project Elements (ranging from 1- unacceptable to 5-excellent).
What I need to do is – based on the Category, apply the appropriate Weighting factor to the Project Element Scores (lookup table 3) and output that value into another column (called Project Element Weighted Score).
Lookup table 1: | |
Lifecycle | Category |
1-Investigation | 1-Conceive |
2-Definition | 2-Design |
3-Implementation | 3-Build |
4-Production | 3-Build |
5-Frozen | 4-Manage |
6-Phase out Started | 4-Manage |
7-Phased out | 4-Manage |
Lookup table 2:
Category | Project Elements | Score | Output value | weighting |
1-Conceive | Personnel | 5 | 5 | 1 |
1-Conceive | Personnel | 4 | 4 | |
1-Conceive | Personnel | 3 | 3 | |
1-Conceive | Personnel | 2 | 2 | |
1-Conceive | Personnel | 1 | 1 | |
1-Conceive | R&D | 5 | 5 | 5 |
1-Conceive | R&D | 4 | 4 | |
1-Conceive | R&D | 3 | 3 | |
1-Conceive | R&D | 2 | 2 | |
1-Conceive | R&D | 1 | 1 | |
1-Conceive | Infrastructure | 5 | 5 | 2 |
I have in the weighted score field on the main spreadsheet and a VLOOKUP formula that was working prior to introducing the Weighting by Category concept. I can’t figure out how to include a lookup to to get the category and the corresponding weighting factor and use the correct weighting factor to apply to the score.
I hope I’ve been clear enough - I cannot send my spreadsheet as it has c
Screenshot of main worksheet
