VLOOKUP help required

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:

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

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

Recommended Solution: INDEX/MATCH with Multiple Criteria

For multi-criteria lookups, INDEX/MATCH (or the newer XLOOKUP if you have Microsoft 365) is more flexible than nested VLOOKUPs.

Option 1: INDEX/MATCH with Array Formula

Assuming your Lookup Table 2 is in a range where:

• Column A = Category
• Column B = Project Element
• Column C = Score
• Column D = Output Value
• Column E = Weighting

excel

=INDEX(WeightingColumn, MATCH(1, (CategoryColumn=YourCategory)*(ElementColumn=YourElement)*(ScoreColumn=YourScore), 0))

Example (adjust cell references to your actual layout):

excel

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

Note: In older Excel versions, press Ctrl+Shift+Enter to enter this as an array formula. In Excel 365/2019+, just press Enter.

Option 2: SUMPRODUCT (No Array Entry Needed)

excel

=SUMPRODUCT(($A$2:$A$100=CategoryCell)*($B$2:$B$100=ElementCell)*($C$2:$C$100=ScoreCell)*($E$2:$E$100))

Option 3: XLOOKUP with Concatenation (Excel 365/2019+)

Create a helper column in your lookup table that concatenates Category + Element + Score, then use:

excel

=XLOOKUP(CategoryCell&ElementCell&ScoreCell, HelperColumn, WeightingColumn, "Not Found")

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

My Recommendation

Use INDEX/MATCH with multiple criteria — it's the most robust solution for your scenario and works across most Excel versions.