Excel Lookups Explained: VLOOKUP vs XLOOKUP, INDEX/MATCH, and Image Lookups
Lookups are where most people’s Excel skills either level up or fall apart. Once you can ask a spreadsheet “go find this value and bring me back the matching one,” you stop copying and pasting data by hand and start letting Excel do the boring work. The trouble is there are several ways to do it: VLOOKUP, XLOOKUP, and INDEX/MATCH, and they all sound interchangeable until one of them breaks on you.
This guide walks through each technique in plain English, with a short video for each so you can watch it done and then read why it works. We even cover the fun stuff at the end: making Excel swap out images based on what you select. Whether you’re cleaning up a price list, building a dashboard, or just trying to match two sheets of data, this is the toolkit you want.
VLOOKUP vs XLOOKUP: The Workhorse and Its Replacement
VLOOKUP, short for “vertical lookup,” has been the go-to for decades. You give it a value to find, a table to search, and a column number to return from, and it scans down the first column until it finds a match. It’s simple and fast, but it carries two well-known headaches: it can only look to the right of the lookup column, and it references the return column by a hard-coded number. Insert a column in the middle of your table and that number quietly points at the wrong data.
XLOOKUP is Microsoft’s modern answer, and it fixes both problems. Instead of counting columns, you hand it a lookup range and a separate return range, so it can pull data from either side of your match and won’t break when you rearrange columns. It also has a clean built-in way to handle “not found” results, so you’re not wrapping everything in an extra IFERROR. The video above shows the same lookup done both ways side by side. If your version of Excel or Microsoft 365 supports XLOOKUP, it’s worth making it your default.
INDEX & MATCH: The Flexible Power Combo
Before XLOOKUP existed, the power users’ answer to VLOOKUP’s limitations was to pair two functions together: INDEX and MATCH. Think of it as a two-step team. MATCH finds the position of your value, for example “this name is the 7th row in the list,” and INDEX grabs whatever sits at that position in any column you point it at. Because the lookup column and the return column are independent, INDEX/MATCH can look left, right, up, or down without complaint.
The video above breaks the combination down step by step so it stops feeling like a magic incantation. The payoff is reliability: insert or move columns and the formula keeps working, because it’s tracking positions rather than fixed column numbers. Even in a world with XLOOKUP, INDEX/MATCH is still worth knowing. It shows up constantly in older spreadsheets you’ll inherit, and it handles two-way lookups (matching on both a row and a column) more naturally than anything else.
Changing Images Using VLOOKUP
Here’s where lookups get genuinely fun. Most people assume lookups can only return text or numbers, but with a little setup you can make a picture change automatically based on a dropdown selection. Imagine choosing a product name from a list and watching its photo appear next to it, or picking an employee and seeing their headshot pop up. That’s a VLOOKUP doing the matching behind the scenes.
The trick, shown in the video, combines a VLOOKUP with a named range and Excel’s ability to link a pasted image to a cell. You build a small table that pairs each name with its image, use VLOOKUP to find the right one, and point a linked picture at the result so it refreshes whenever your selection changes. It looks like sorcery to anyone watching, but it’s just the same lookup logic you already learned, applied to a picture instead of a price.
Changing Images Using Formulas
This final walkthrough generalizes the image trick beyond VLOOKUP. The same dynamic-image approach works with whatever lookup formula you prefer, INDEX/MATCH or XLOOKUP included, by feeding the result into a named range that a linked image references. The video shows how to wire the formula and the named range together so the picture follows your data.
Why bother learning more than one method? Because each fits a different spreadsheet. A formula-driven image gallery is perfect for product catalogs, interactive dashboards, ID badges, or any report where a visual makes the data click for the person reading it. Once you understand that an image can be driven by a formula, you can build polished, interactive workbooks that look like custom software, using nothing but Excel.
Let AI Write the Formula For You
If your eyes glaze over at the syntax, here’s the good news: you no longer have to memorize it. Modern AI tools can write these formulas from a plain-English description. Tell Microsoft Copilot inside Excel, ChatGPT, or Claude something like “look up the price for the product name in cell B2 from my Products sheet and return the value, blank if not found,” and it will hand you a working XLOOKUP or INDEX/MATCH formula you can paste straight in. It’s also great for explaining a formula you inherited and can’t make sense of.
To get more out of these tools, grab our quick-reference guides: the Claude AI cheat sheet and the ChatGPT cheat sheet. They’ll help you phrase requests so you get clean, correct formulas the first time.
Want AI and automation working inside your business, not just in a spreadsheet, but across your whole operation? Book a discovery call and we’ll show you where it can save your team real time.
Frequently Asked Questions
What’s the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches down the first column of a table and returns a value from a column you specify by number, but it can only look to the right and breaks if you insert or move columns. XLOOKUP uses separate lookup and return ranges, so it can pull data from either direction, survives column changes, and has built-in handling for “not found” results. If your version of Excel or Microsoft 365 supports XLOOKUP, it’s the better default.
When should I use INDEX/MATCH instead?
Reach for INDEX/MATCH when you’re working in older spreadsheets that don’t have XLOOKUP, when you need to look up a value to the left of your match, or when you need a two-way lookup that matches on both a row and a column at once. It’s also worth knowing simply because it appears in so many existing workbooks you’ll be asked to maintain.
Can Excel really change a picture based on a dropdown?
Yes. By pairing a lookup formula (VLOOKUP, XLOOKUP, or INDEX/MATCH) with a named range and a linked picture, you can make an image swap automatically whenever you change a selection. It’s commonly used for product catalogs, employee directories, ID badges, and interactive dashboards.
Do I still need to learn formulas if AI can write them?
It helps to understand the basics so you can sanity-check what the AI gives you and fix it when a result looks off, but tools like Copilot, ChatGPT, and Claude can absolutely write and explain these lookups from a plain-English request. Think of AI as a fast assistant that handles the syntax while you stay in charge of the logic.
Ready to Put This to Work?
Excel lookups are one small example of how the right tools and a little know-how save your team hours every week. If you’d like AI and automation doing that kind of heavy lifting across your whole business, let’s talk. Book a free discovery call or call us in Houston at 281-367-8253.