Two-way and multi-criteria lookups

Why this lesson matters: Two-way and multi-criteria lookups

Match both row and column dimensions or several business conditions. In this module, the skill supports a larger goal: Build flexible one-way, two-way, and multi-criteria retrieval logic. The practical application is to return a regional price for a selected product and month.

=INDEX(B2:M20,MATCH(H2,A2:A20,0),MATCH(H3,B1:M1,0))

Build it step by step

Work from the business question toward the formula or tool. Keep the source data separate from the output and make every important assumption visible to the reviewer.

• Choose the smallest lookup and return arrays that solve the question.
• Use explicit not-found behaviour.
• Test duplicate, missing, and boundary matches before release.

Check the result and common failure points

Compare a sample of advanced lookup results with a manual filter of the source data. Also test a known record, a missing or blank value, and a boundary case. Confirm that ranges are the same size, units are consistent, and copied references move only where intended.

• Compare at least one result with a manual calculation or filtered source record.
• Test blanks, zeros, duplicates, errors, and the exact rule boundary.
• Reconcile row counts or totals before relying on the output.

Use the result in a decision

Return a regional price for a selected product and month. Label the output clearly, state its period and unit, and explain the action it supports. A technically correct result is only complete when another person can understand, refresh, and review it.