SheetSense

Formula explained · November 20, 2026

What this formula does

fx
=IFERROR(INDEX(Orders!D:D,MATCH(1,(Orders!A:A=A2)*(Orders!B:B=B2),0)),"n/a")

Step by step

1. (Orders!A:A=A2) compares every cell in column A of the Orders sheet with the value in A2, producing an array of TRUE/FALSE values.

2. (Orders!B:B=B2) does the same for column B of Orders against B2.

3. Multiplying the two arrays with * turns TRUE/FALSE into 1/0 and acts as a logical AND. A row gets 1 only if both column A and column B match.

4. MATCH(1, ..., 0) finds the position of the first row whose result is 1, using an exact match.

5. INDEX(Orders!D:D, position) returns the value from column D of Orders at that position.

6. IFERROR(..., "n/a") returns the text "n/a" if no row matches both criteria, or if any other error occurs.

In short, this is a two-criteria lookup. It finds the first Orders row where column A equals A2 and column B equals B2, then returns that row's column D value, or "n/a" if there is no match.

The same logic in four tools

Excel
=IFERROR(INDEX(Orders!D:D,MATCH(1,(Orders!A:A=A2)*(Orders!B:B=B2),0)),"n/a")
Google Sheets
=IFERROR(INDEX(Orders!D:D,MATCH(1,ARRAYFORMULA((Orders!A:A=A2)*(Orders!B:B=B2)),0)),"n/a")
SQL
SELECT d.*,
       COALESCE(
         (SELECT CAST(o.D AS VARCHAR(255))
            FROM Orders o
           WHERE o.A = d.A
             AND o.B = d.B
           ORDER BY o.row_num
           LIMIT 1),
         'n/a') AS result
  FROM data d;
-- Assumes Orders has a row_num column that preserves sheet order, so the FIRST match is returned like MATCH does.
-- Use TOP 1 in SQL Server instead of LIMIT 1.
Python (pandas)
import pandas as pd

# orders: DataFrame for the Orders sheet with columns A, B, D
# df: the current sheet with columns A, B
lookup = (orders.drop_duplicates(subset=['A', 'B'], keep='first')
                [['A', 'B', 'D']]
                .rename(columns={'D': 'result'}))

df = df.merge(lookup, on=['A', 'B'], how='left')
df['result'] = df['result'].astype(object).where(df['result'].notna(), 'n/a')

Watch out for

  • !In Excel 2019 and earlier, this must be entered as an array formula with Ctrl+Shift+Enter. Without it, the formula silently returns "n/a". Google Sheets needs the ARRAYFORMULA wrapper shown above.
  • !Whole-column references like A:A, B:B and D:D force about 1 million row comparisons per cell, which can make large sheets very slow. Use bounded ranges such as A2:A5000 or a Table instead.
  • !Only the first matching row is returned. Duplicate A/B combinations in Orders are silently ignored, so check for duplicates if every match matters.
  • !IFERROR hides every error, not just 'not found'. Mismatched ranges, #REF!, or #VALUE! errors will also show as "n/a" and mask real problems.
  • !Type mismatches cause false misses: the number 123 does not equal the text "123", and trailing spaces break matches. Also, a matching row with a blank D cell returns 0 in Excel, while the pandas and SQL versions return "n/a".

Inherited a workbook full of these?

SheetSense explains any formula, writes new ones from a sentence, and cleans the CSV exports that feed them. The translator is free to try.