Lookup values across tabs

What you will build

Look up prices for multiple SKUs on Orders from a price table on Catalog while entering only one formula in anchor cell C2. The lesson uses fictional data so every step can be reproduced safely.

Learning objective

Use ARRAYFORMULA, IFNA, and VLOOKUP for exact-match lookup across two tabs, understand each parameter, how SKUs are evaluated row by row, and how a missing SKU is handled.

Prepare the sample data

Open the exact source workbook below or make your own copy. Orders contains SKU and Qty; Catalog contains SKU and Price. Keep the source workbook unchanged and practice on your copy.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The practical-formulas-10 source workbook in Google Sheets with Orders selected, showing SKU, Qty, and an empty Price column.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, open Catalog and confirm the lookup table is A1:B4 with three unique SKUs: P01 = 12, P02 = 35, and P03 = 22.

The _result workbook with Catalog selected, showing the exact SKU and Price table used by VLOOKUP.

Step 2: Open Orders, select C2, and enter the formula below once. The results spill through C2:C4 automatically; do not drag the fill handle or copy the formula down.

Formula to try

In Orders!C2, enter the formula below. Keep C2:C4 clear beforehand so the spill range is not blocked.

=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

The _result workbook with Orders!C2 selected; the formula bar shows the complete ARRAYFORMULA and the spill results are 35, 12, and 22.

How this formula works=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

Overall meaning of the whole formula
- Process all of A2:A4 at once: leave the result blank when the source SKU is blank; otherwise look up the matching value in the Catalog tab, and display "Check SKU" when no match is found.
ARRAYFORMULA(array_formula)
- array_formula is required; it accepts an expression that can produce one or more values. Here it is IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")).
- ARRAYFORMULA lets one formula in C2 process all three SKUs in A2:A4 together.
- The three-item result spills through C2:C4, so there is no fill-handle or copy-down step.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression is required; here it is A2:A4="", which produces a TRUE/FALSE array indicating whether each SKU cell is blank.
- value_if_true is required; this lesson passes "" so a blank SKU returns a blank price.
- value_if_false is optional; this lesson passes IFNA(VLOOKUP(...),"Check SKU") so only nonblank SKUs are looked up.
VLOOKUP(search_key, range, index, [is_sorted])
- search_key is required; this lesson passes A2:A4, so P02, P01, and P03 are evaluated in row order.
- range is required; this lesson passes Catalog!$A$2:$B$4. Its first column contains the SKU keys and its second column contains the Price values to return.
- index is required; 2 tells VLOOKUP to return the second column of the Catalog range.
- is_sorted is optional; this lesson passes FALSE to require an exact SKU match.
- The dollar signs in Catalog!$A$2:$B$4 fix the lookup-table boundaries. The lesson uses a spill formula rather than filling down, but the Catalog range is still explicitly fixed.
IFNA(value, value_if_na)
- value is required; here it is VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE).
- value_if_na is required; this lesson passes "Check SKU". It is used only when VLOOKUP returns #N/A because a SKU was not found.
- IFNA does not replace unrelated error types, which makes it precise for the missing-SKU case.
Whole-expression evaluation
- A2 = P02: VLOOKUP finds P02 in Catalog!A2:A4 and returns 35 from the second column.
- A3 = P01: the lookup result is 12.
- A4 = P03: the lookup result is 22.
- The three search keys and three returned values stay aligned in row order, producing {35; 12; 22}.
- If one SKU is changed to a code absent from Catalog, VLOOKUP returns #N/A for that element and IFNA replaces only that element with Check SKU.
Why the displayed final result appears
- The final array is {35; 12; 22} and spills from C2 through C4.
- During the audit, changing Orders!A2 from P02 to P99 changed C2 to Check SKU; restoring P02 returned C2 to 35.
- This confirms that the visible output comes from the real Catalog table and one formula entered only in C2.

Checkpoint

SKU	Qty	Price
P02	2	35
P01	3	12
P03	1	22

Expected result: Price spills through C2:C4 as 35, 12, and 22.

Common mistakes and fixes

If Check SKU appears, confirm the code in Orders exactly matches a SKU in Catalog. FALSE requires exact matching, so a different code or a missing SKU will not be approximately matched.

Independent practice

Temporarily change Orders!A2 from P02 to P99 and confirm C2 displays Check SKU; then restore P02 and confirm C2 returns to 35.

Key takeaways

✓Catalog stores the SKU-to-Price lookup table; Orders uses it to retrieve prices.
✓FALSE makes VLOOKUP require an exact SKU match.
✓IFNA replaces only the not-found case with Check SKU.
✓One ARRAYFORMULA in C2 produces all three results: 35, 12, and 22.
✓Do not drag or copy the formula into C3:C4 because the results already spill.

Continue learning

Previous lesson: Compare two lists.

What you will build

Look up prices for multiple SKUs on Orders from a price table on Catalog while entering only one formula in anchor cell C2. The lesson uses fictional data so every step can be reproduced safely.

Learning objective

Use ARRAYFORMULA, IFNA, and VLOOKUP for exact-match lookup across two tabs, understand each parameter, how SKUs are evaluated row by row, and how a missing SKU is handled.

Prepare the sample data

Open the exact source workbook below or make your own copy. Orders contains SKU and Qty; Catalog contains SKU and Price. Keep the source workbook unchanged and practice on your copy.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, open Catalog and confirm the lookup table is A1:B4 with three unique SKUs: P01 = 12, P02 = 35, and P03 = 22.

Step 2: Open Orders, select C2, and enter the formula below once. The results spill through C2:C4 automatically; do not drag the fill handle or copy the formula down.

Formula to try

In Orders!C2, enter the formula below. Keep C2:C4 clear beforehand so the spill range is not blocked.

=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

How this formula works=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

Overall meaning of the whole formula
- Process all of A2:A4 at once: leave the result blank when the source SKU is blank; otherwise look up the matching value in the Catalog tab, and display "Check SKU" when no match is found.
ARRAYFORMULA(array_formula)
- array_formula is required; it accepts an expression that can produce one or more values. Here it is IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")).
- ARRAYFORMULA lets one formula in C2 process all three SKUs in A2:A4 together.
- The three-item result spills through C2:C4, so there is no fill-handle or copy-down step.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression is required; here it is A2:A4="", which produces a TRUE/FALSE array indicating whether each SKU cell is blank.
- value_if_true is required; this lesson passes "" so a blank SKU returns a blank price.
- value_if_false is optional; this lesson passes IFNA(VLOOKUP(...),"Check SKU") so only nonblank SKUs are looked up.
VLOOKUP(search_key, range, index, [is_sorted])
- search_key is required; this lesson passes A2:A4, so P02, P01, and P03 are evaluated in row order.
- range is required; this lesson passes Catalog!$A$2:$B$4. Its first column contains the SKU keys and its second column contains the Price values to return.
- index is required; 2 tells VLOOKUP to return the second column of the Catalog range.
- is_sorted is optional; this lesson passes FALSE to require an exact SKU match.
- The dollar signs in Catalog!$A$2:$B$4 fix the lookup-table boundaries. The lesson uses a spill formula rather than filling down, but the Catalog range is still explicitly fixed.
IFNA(value, value_if_na)
- value is required; here it is VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE).
- value_if_na is required; this lesson passes "Check SKU". It is used only when VLOOKUP returns #N/A because a SKU was not found.
- IFNA does not replace unrelated error types, which makes it precise for the missing-SKU case.
Whole-expression evaluation
- A2 = P02: VLOOKUP finds P02 in Catalog!A2:A4 and returns 35 from the second column.
- A3 = P01: the lookup result is 12.
- A4 = P03: the lookup result is 22.
- The three search keys and three returned values stay aligned in row order, producing {35; 12; 22}.
- If one SKU is changed to a code absent from Catalog, VLOOKUP returns #N/A for that element and IFNA replaces only that element with Check SKU.
Why the displayed final result appears
- The final array is {35; 12; 22} and spills from C2 through C4.
- During the audit, changing Orders!A2 from P02 to P99 changed C2 to Check SKU; restoring P02 returned C2 to 35.
- This confirms that the visible output comes from the real Catalog table and one formula entered only in C2.

Checkpoint

SKU	Qty	Price
P02	2	35
P01	3	12
P03	1	22

Expected result: Price spills through C2:C4 as 35, 12, and 22.

Common mistakes and fixes

If Check SKU appears, confirm the code in Orders exactly matches a SKU in Catalog. FALSE requires exact matching, so a different code or a missing SKU will not be approximately matched.

Independent practice

Temporarily change Orders!A2 from P02 to P99 and confirm C2 displays Check SKU; then restore P02 and confirm C2 returns to 35.

Key takeaways

✓Catalog stores the SKU-to-Price lookup table; Orders uses it to retrieve prices.
✓FALSE makes VLOOKUP require an exact SKU match.
✓IFNA replaces only the not-found case with Check SKU.
✓One ARRAYFORMULA in C2 produces all three results: 35, 12, and 22.
✓Do not drag or copy the formula into C3:C4 because the results already spill.

Continue learning

Previous lesson: Compare two lists.

Lookup values across tabs

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Lookup values across tabs

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments