Google Sheets中LOOKUP(2)公式报错求助：原Excel公式无法适配

The issue with your original LOOKUP(2;1/(...)) formula is that Google Sheets handles error values and array sorting differently than Excel. While Excel ignores #DIV/0! errors and finds the last matching value, Google Sheets often throws an error because it doesn’t reliably process this pattern when the lookup array isn’t sorted in ascending order.

Here are two reliable alternatives that work seamlessly in Google Sheets (and modern Excel):

Option 1: INDEX + MATCH (Cross-Platform Compatible) #

This approach explicitly searches for the first row where both criteria are met, then returns the corresponding price from column I.

=INDEX(matbaaya_giden!$I$2:$I$2000, MATCH(1, ARRAYFORMULA((matbaaya_giden!$B$2:$B$2000=$B2)*(matbaaya_giden!$G$2:$G$2000=G2-1)), 0))

Breakdown: #

• ARRAYFORMULA((matbaaya_giden!$B$2:$B$2000=$B2)*(matbaaya_giden!$G$2:$G$2000=G2-1)): Creates an array of 1s (where both product name and adjusted serial number match) and 0s (where they don’t).
• MATCH(1, ..., 0): Finds the position of the first 1 (first matching row) in the array.
• INDEX(matbaaya_giden!$I$2:$I$2000, ...): Retrieves the price from column I at that matching position.

Option 2: XLOOKUP (Simpler, Modern Approach) #

XLOOKUP is designed for flexible lookups and supports multiple criteria directly. It’s available in both Google Sheets and modern Excel versions.

=XLOOKUP(1, ARRAYFORMULA((matbaaya_giden!$B$2:$B$2000=$B2)*(matbaaya_giden!$G$2:$G$2000=G2-1)), matbaaya_giden!$I$2:$I$2000)

Breakdown: #

• We search for 1 in the criteria array (same as above).
• XLOOKUP returns the corresponding value from column I. By default, it picks the first match, but you can adjust this if needed.

Handle No-Match Errors #

To avoid #N/A errors when no matching row exists, wrap the formula in IFERROR (for INDEX/MATCH) or add a default value (for XLOOKUP):

• For INDEX/MATCH:

  =IFERROR(INDEX(matbaaya_giden!$I$2:$I$2000, MATCH(1, ARRAYFORMULA((matbaaya_giden!$B$2:$B$2000=$B2)*(matbaaya_giden!$G$2:$G$2000=G2-1)), 0)), "No price found")
  

• For XLOOKUP:

  =XLOOKUP(1, ARRAYFORMULA((matbaaya_giden!$B$2:$B$2000=$B2)*(matbaaya_giden!$G$2:$G$2000=G2-1)), matbaaya_giden!$I$2:$I$2000, "No price found")
  

内容的提问来源于stack exchange，提问作者Rheago