Lookups: pulling a price without retyping it

Lookups: Pulling a Price Without Retyping It

Okay. Last lesson we counted stock and set up something that tracks what moves. Now we're gonna fix a different annoying thing: typing the same price into your spreadsheet over and over.

Here's the problem. You've got your product list with everything on it, name, price, whatever. Then you make a sales log, or an order form, or an invoice tab, and every single time somebody buys something you type the product name AND the price again by hand. That's fine for like ten transactions. It's a nightmare at two hundred. And it's how you end up with three different prices for the same candle because you fat-fingered one of them at 9pm.

The fix is a lookup formula. It grabs the price from your product list automatically, based on the product name you type. You never retype a price again. Your future self says thank you.

The formula you actually need

I'm gonna have you use VLOOKUP. There's a newer one called XLOOKUP that some people love, and it's fine, but VLOOKUP is the one that's been around forever, works everywhere, and is the one I can troubleshoot in my sleep. Stick with it for now.

It looks like this:

`` =VLOOKUP(search_item, range_to_search, column_number, FALSE) ``

Translated into human:

• search_item — what are you looking for. Usually a cell reference, like the product name you just typed in your sales log.
• range_to_search — where's the table with the answer. This should be your product list, and grab the whole thing, name column through price column.
• column_number — counting from the left side of your range, which column has the price. If your product list starts with name in column A and price is in column C, that's column 3.
• FALSE — always type FALSE at the end. It means "only give me an exact match." Leave it off and Sheets will sometimes hand you a confident, totally wrong price. We talked about that a few lessons back. A spreadsheet will lie to you politely if you let it.

So a real one might look like:

`` =VLOOKUP(A2, ProductList!A:C, 3, FALSE) ``

That's saying: look at whatever's in A2 on this sheet, go find it in the ProductList tab, and bring me back what's in the third column.

Setting it up, step by step

1. Go to your sales log or order tab, wherever you're typing product names.
2. Add a column next to it called Price.
3. In the first row, type your VLOOKUP formula pointing at your product list.
4. Type a product name in the row next to it and watch the price show up on its own.
5. Drag the little box in the corner of that formula cell down the column so every row has it.

Everybody with me so far? If your cell just shows #N/A, don't panic, that's not broken, that's Sheets telling you it couldn't find a match. Usually it's a spelling thing. "Candle - Vanilla" and "Candle-Vanilla" are not the same to a spreadsheet even though they're the same to you. This is exactly the kind of thing I mean when I say naming things clearly matters. Future-you, typing fast at the end of a shift, needs your product names to be dead consistent.

A quick opinion

You don't need anything fancier than this. There are people online who will tell you to nest lookups inside other lookups inside IF statements and honestly, if you don't fully understand what you're building, you'll break it in three months and have no idea why. Ninety percent of running a small business is SUM, a lookup, and knowing roughly what your numbers should look like. That's it. That's most of it.

A story, because I have to tell you this one

I once spent a whole Saturday building the most gorgeous dashboard for a garage-sale fundraiser. Colors, categories, a running total that updated itself, the works. I was really proud of it. Set it up on a laptop at the check-in table.

Nobody looked at it. Not once. All day. People just wanted a clipboard and a pen.

I was quiet the rest of that day, ngl. But it taught me something I still think about every time I build one of these trackers for someone. The fancy version isn't the point. The person actually using the thing is the point. If a lookup formula means your teenager working the register can find the right price without calling you over, that's worth way more than the prettiest sheet in the world that nobody opens.

So build the lookup. Test it with weird inputs, misspell something on purpose, see what breaks. Get it working ugly. We'll make it look nice later, and by "later" I mean way later, because functioning always comes before pretty in this class.

Before next time: get a working VLOOKUP pulling at least one price into your sales or order tab, even if it's only hooked up for two or three products. We'll build it out together next lesson.