VLOOKUP help required

Question

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

brenda hines · Answer

Screenshot of main worksheet&nbsp;

vihargadhesariya · Answer

Try SUMPRODUCT&nbsp;= D4 * SUMPRODUCT(
   (Table3[Category]=$H$4) *
   (Table3[ProjectElement]=$C4) *
   (Table3[weighting])
)Or INDEX/MATCH:= D4 *
  INDEX(
    Table3[weighting],
    MATCH(
      1,
      INDEX(
        (Table3[Category]=$H$4) *
        (Table3[ProjectElement]=$C4),
      0),
    0)
  )&nbsp;

ghulammujtaba · Answer

You're dealing with a multi-criteria lookup, which is a common challenge in Excel. Let me help you understand your options.

The Problem

You need to look up a value based on three criteria:

Category (from the Project Lifecycle)
Project Element (e.g., Personnel, R&D, Infrastructure)
Score (1-5)

Then apply the appropriate weighting factor to calculate a weighted score.

Calculating the Weighted Score

Once you retrieve the weighting factor, multiply it by the score:

excel

=ScoreCell * [Weighting Formula from above]

Or combine it all in one formula:

excel

=ScoreCell * INDEX($E$2:$E$100, MATCH(1, ($A$2:$A$100=CategoryCell)*($B$2:$B$100=ElementCell)*($C$2:$C$100=ScoreCell), 0))

Quick Comparison

Method	Pros	Cons
INDEX/MATCH	Flexible, handles multiple criteria	Slightly complex syntax
SUMPRODUCT	No Ctrl+Shift+Enter needed	Returns 0 if no match (not an error)
XLOOKUP	Clean syntax, easy to read	Only available in newer Excel
Nested VLOOKUP	Familiar	Gets messy with 3+ criteria, less efficient