Find the price where product is in column A and region is in column B, matching both keys

公式

=INDEX(C2:C500, MATCH(1, (A2:A500=E2)*(B2:B500=F2), 0))

说明

Multiplying two TRUE/FALSE arrays produces 1 only on the row where both keys match. MATCH finds that 1, and INDEX returns the price. In older Excel confirm with Ctrl+Shift+Enter.

步骤

  1. E2 holds the product, F2 the region.
  2. (A=E2)*(B=F2) is 1 on the matching row.
  3. MATCH(…, 0) finds the first exact 1.
  4. INDEX returns the price from column C.

变体

Excel 365 XLOOKUP with concatenated key

Builds a single helper key without a helper column.

=XLOOKUP(E2&"|"&F2, A2:A500&"|"&B2:B500, C2:C500)

SUMIFS when the price should add

Use this when multiple rows can match and you want the total.

=SUMIFS(C2:C500, A2:A500, E2, B2:B500, F2)

Return N/A if missing

Wraps the lookup so missing pairs do not show #N/A.

=IFERROR(INDEX(C2:C500, MATCH(1, (A2:A500=E2)*(B2:B500=F2), 0)), "N/A")