How to Pull a Price From Another Sheet Automatically

Type a code on one sheet, and the price appears from another. That's one formula.

=INDEX('Price list'!$D$2:$D$13, MATCH($A2, 'Price list'!$A$2:$A$13, 0))

MATCH finds which row the code sits on. INDEX returns the value on that row of the price column. Everything else in this guide is about making it survive contact with real data.

Only the yellow cells are typed. The total reads 6,126.90 and is missing a line, with nothing on the sheet to say so.

The two halves

Part	Job
MATCH($A2, codes, 0)	Which row is this code on? Returns a position — 8, not a value.
INDEX(prices, 8)	Give me the 8th price.

The 0 at the end of MATCH means exact match. Leave it out and MATCH assumes your list is sorted and returns the nearest value at or below what you asked for — which on an unsorted price list returns a real price for the wrong product, with no error to warn you. Never omit it.

Why this rather than VLOOKUP: the price column can sit anywhere relative to the code column, including to its left, and inserting a column doesn't break anything. The full comparison is here.

Pointing at another sheet

A reference to another sheet is the sheet name, an exclamation mark, then the range:

=Prices!$D$2:$D$13

If the sheet name contains a space, it needs single quotes around it:

='Price list'!$D$2:$D$13

You don't have to type any of this. Start the formula, and when you reach the range, click the other sheet's tab and drag over the cells — Excel writes the reference, quotes included.

⚠️ Lock the lookup ranges with $, and leave the lookup cell relative. $A2 moves down as you copy; $D$2:$D$13 must not. Copy a formula with unlocked ranges down twenty rows and the search range slides off the bottom of the price list, so codes near the end stop being found. It's the most common failure in this whole pattern, and it fails quietly.

Making it presentable

Two problems appear the moment real people use the sheet: empty rows show #N/A, and so do typos. The finished formula handles both:

=IF($A2="", "",
   IFERROR(INDEX('Price list'!$D$2:$D$13,
                 MATCH($A2, 'Price list'!$A$2:$A$13, 0)), ""))

Read it outside in. No code, show nothing. Code present but not found, show nothing rather than an error.

One deliberate exception

In the description column, don't return a blank. Return words:

=IF($A2="", "",
   IFERROR(INDEX('Price list'!$B$2:$B$13,
                 MATCH($A2, 'Price list'!$A$2:$A$13, 0)), "Code not found"))

A row of blanks looks like a row you haven't filled in yet. "Code not found" tells you the code is wrong. One place in the sheet should say what happened, and the description column is the one people read.

This matters more than it sounds. In the example above, the order total is 6,126.90 — and that figure quietly excludes the unmatched line. A total that's wrong while looking entirely reasonable is worse than one that shows an error, and IFERROR used everywhere is how you get one.

Stop the typos instead of catching them

Better than handling a bad code is making it impossible to enter one. Select the code column, then Data → Data Validation → List, and point the source at the price list's code column.

Now the column is a drop-down. Codes are chosen rather than typed, #N/A largely stops happening, and the person filling the sheet doesn't need to know any codes by heart.

Keep the IFERROR anyway — validation doesn't apply to pasted values, and pasting is how bad data usually arrives.

Pulling several fields at once

Most of the time you want the description, the unit and the price. The obvious approach repeats the whole formula three times, which means MATCH runs three times to find the same row.

Do the search once, in a helper column:

H2:  =IFERROR(MATCH($A2, 'Price list'!$A$2:$A$13, 0), "")

B2:  =IF($H2="", "", INDEX('Price list'!$B$2:$B$13, $H2))
C2:  =IF($H2="", "", INDEX('Price list'!$C$2:$C$13, $H2))
D2:  =IF($H2="", "", INDEX('Price list'!$D$2:$D$13, $H2))

Three lookups become one. On a few dozen rows you won't notice; on a few thousand across several columns it's the difference between instant and a pause every time you type. Hide the helper column once it works.

Does this work in Google Sheets?

Yes, identically — INDEX, MATCH, IFERROR and cross-sheet references all behave the same, quotes included.

Pulling from a different file is where they diverge. Excel can do it, but the formula only refreshes while the source workbook is open and breaks if the file moves, so copying the price list into the same workbook is almost always the right answer. Sheets uses IMPORTRANGE, which is a genuinely different tool and worth its own guide.

When it goes wrong

#N/A on a code you can see on the other sheet

The two values differ invisibly — a trailing space, or one is text and the other a number. The same three causes covered here apply exactly.

Codes at the bottom of the list stop being found

Unlocked ranges. The formula needs $A$2:$A$13. Click into the reference and press F4.

#REF! everywhere

The price list sheet was renamed or deleted. Renaming normally updates formulas automatically — unless the name was typed into a formula as text, which is one more reason to build references by clicking.

The wrong price, with no error at all

The 0 is missing from MATCH. This is the dangerous one: it returns a plausible value from the wrong row and nothing looks broken.

The formula shows as text

The cell was formatted as Text before you typed. Set it to General, then press F2 and Enter to re-enter it.

Common questions

What if a code appears twice on the price list?

MATCH returns the first one and ignores the second. Codes must be unique — COUNTIF will find the duplicates before they cost you money.

How do I handle prices that change?

A lookup returns today's price, not the price when the order was placed. If that distinction matters — and in anything you'll be asked to justify later, it does — paste the pulled prices as values once the order is agreed. Keeping historical prices live in a lookup means last month's orders silently re-price themselves.

Can the price list live in a different workbook?

It can, and it will cause trouble. The link breaks when the file is moved or renamed, and the values go stale when it's closed. Copy the list in and refresh it deliberately.

How do I add rows to the price list later?

Insert them inside the existing range rather than under it — a row added below row 13 falls outside $A$2:$A$13. Or turn the price list into a Table (Ctrl+T) and reference the Table name, which grows on its own.

Can I pull a price based on two things, like code and supplier?

Yes, but not with plain MATCH. Either add a helper column joining the two values on both sheets, or use SUMIFS if the result is a number — two conditions is what SUMIFS is for.

The short version

• INDEX(prices, MATCH(code, codes, 0)) is the pattern.
• The 0 is not optional. Without it you get wrong answers, not errors.
• Lock the ranges, leave the lookup cell relative.
• Guard blank rows with IF, missing codes with IFERROR.
• Let one column say "Code not found" — a sheet of blanks hides a short total.
• A drop-down on the code column prevents most errors before they happen.
• MATCH once into a helper column when you're pulling several fields.

Next steps: the mechanics underneath this are INDEX MATCH, and when a code won't match despite looking identical, the three usual causes are here. If you're pricing work rather than ordering it, the inventory tracker uses this same lookup to name items in its movement log.