If you’re tired of convoluted formulas and want to make your Vlookup in Google Sheets look cleaner, then using Named Ranges is the way to go. But that’s not the only benefit you’ll get from this powerful combination. Let me share with you some hidden perks that I discovered.
The Power of Vlookup and Named Ranges
Imagine you’re at a busy metro station, trying to find the train timing or route. You already have the train number or name, but you need the information displayed on the panel. In a way, this is how Vlookup works. The dataset is the display panel, and your search key is the information you already have.
To make it even easier, you can name your dataset. Let’s say you name it « train_info ». This is what we call Named Ranges. Now let me show you how to use Named Ranges in Vlookup in Google Sheets.
The Basic Use of Named Ranges in Vlookup
Take a look at the screenshot below. I have a price list of industrial items in columns A, B, and C. Column A contains the item codes, column B contains the corresponding items, and column C contains the unit prices.
Now, suppose I have a search key that is the code of one item in cell E3. I want to find the unit price for that item and display it in cell F3. Here’s the formula without Named Ranges:
=vlookup(E3,A2:C5,3,0)But we can make it even simpler by using Named Ranges. Here’s how:
- Select the data range A2:C5.
- Go to the « Data » menu and choose « Named ranges… »
- Enter the name « pricelist » and click « Done ».
Now you can use this Named Range in your Vlookup formula:
=vlookup(E3,pricelist,3,0)As you can see, using Named Ranges makes the formula cleaner and easier to understand. But that’s not all. Let me share two more benefits of using Named Ranges in Vlookup.
The Main Benefits of Using Named Ranges
Use the Same Vlookup Formula Across Sheet Tabs
Imagine you have your search key in cell E3 on Sheet 5, and your source data with the Named Range is on Sheet 1. Without Named Ranges, the formula would look like this:
=vlookup(E3,Sheet1!A2:C5,3,0)But with Named Ranges, you can simplify it to:
=vlookup(E3,pricelist,3,0)This way, you can use the same formula across different sheet tabs without having to update the range reference every time. Much easier, isn’t it?
Switch Lookup Tables with a Drop-down Menu
In some cases, you may need to switch between different lookup tables based on your needs. For example, you have two tables: « jennie » and « allison ». You can name these ranges and create a drop-down menu that allows you to switch between them.
Here’s how to do it:
- Name the range A3:B5 as « jennie » and D3:E5 as « allison ».
- Create a drop-down menu in cell H2 that contains the names of the two Named Ranges.
- Use the INDIRECT function to dynamically switch between the lookup tables based on the selection in the drop-down menu.
The formula in cell I2 would look like this:
=vlookup(G2,indirect(H2),2,0)With this powerful combination of Vlookup, Named Ranges, and the Indirect function, you can switch between lookup tables effortlessly. Take a look at this animation to see it in action:
And that’s how you can simplify your Vlookup in Google Sheets using Named Ranges. Enjoy the benefits and make your life easier!
To learn more about Named Ranges, check out these additional resources:
Now go ahead and make your formulas shine with Named Ranges!
