Formula explained · November 20, 2026
What this formula does
=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
=IFERROR(INDEX(Orders!D:D,MATCH(1,(Orders!A:A=A2)*(Orders!B:B=B2),0)),"n/a")=IFERROR(INDEX(Orders!D:D,MATCH(1,ARRAYFORMULA((Orders!A:A=A2)*(Orders!B:B=B2)),0)),"n/a")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.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.