Return a product price with VLOOKUP
Create a tab named Products using this catalog. On Orders, enter P-204 in A2.
| Row | A: ID | B: Product | C: Price |
|---|---|---|---|
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-204 | Stapler | 12.00 |
| 4 | P-318 | Markers | 7.25 |
=VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE)Paste the formula in Orders!B2. It returns 12.00. The ID must be in the first column of the search range, and 3 means the third column of that range. FALSE requests an exact match. Use the Google Sheets option in our generator for this supported rightward lookup.
Handle an absent ID deliberately
=IFNA(VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE),"ID missing")With P-999 in Orders!A2, this displays ID missing. Without IFNA, the lookup returns #N/A. This manual wrapper is useful when missing IDs are expected; it does not fix a bad reference. The generator currently outputs the single VLOOKUP formula, without this wrapper.
Count and total order rows
For this separate example, replace the earlier Orders tab contents with these records. Put the count and total formulas on a Summary tab so they do not overwrite the source data:
| Row | A: ID | B: Status | C: Amount |
|---|---|---|---|
| 2 | P-101 | Paid | 9 |
| 3 | P-204 | Unpaid | 12 |
| 4 | P-318 | Paid | 14.50 |
| 5 | P-204 | Paid | 24 |
=COUNTIF(Orders!B2:B5,"Paid")This counts 3 paid orders. It counts rows without considering their amounts.
=SUMIFS(Orders!C2:C5,Orders!B2:B5,"Paid",Orders!A2:A5,"P-204")This adds only paid P-204 orders, returning 24. The unpaid 12 is excluded. These COUNTIF and SUMIFS examples are for manual use; our tool does not generate them yet.
Check tab names, separators, and copied references
For a tab name with spaces, use apostrophes: 'Product Catalog'!$A$2:$C$4. Fix the catalog range with $ before filling the formula down. Keep the order ID reference relative so each row looks up its own ID.
The examples use English function names and commas. Some spreadsheet locales require semicolons between arguments and use a comma as a decimal separator. If you see a formula parse error, check the spreadsheet’s locale and formula separators before changing the lookup logic. Do not replace punctuation inside a quoted text value indiscriminately.
Google’s VLOOKUP documentation explains exact matching and data-type issues. If you need a left lookup, Google Sheets also supports XLOOKUP; that function is outside this generator’s current scope.