H2Epicturelookup1

How to Do a Picture Lookup in Excel

A lookup is one of the more common things you can do in Excel. Whether you’re using VLOOKUP, a combination of INDEX and MATCH, or the new XLOOKUP, there are no shortage of ways to accomplish it. However, in this post, I’ll go over how you can do a lookup that involves pulling in a picture. It’s a bit more complicated to set up but once you’ve figured it out, it should be a breeze.

Step 1: Create a table of the images you would like to use

I’m going to create a tab for images that has two columns — one for the name of the image, while the other will hold the image itself. I’m going to make the rows wide, with a height of 60 just to make sure the cell can fit the entire image. In this example, I’m using some popular corporate logos:

Table with company images.

Step 2: Setup the named ranges

Next, I’ll create named ranges in column B that match the values in column A. In the example above I don’t have any spaces but if I did, I would replace them with an underscore to make sure there are no gaps. In addition to creating a named range for each individual logo, I will also create a named range that contains all the values in column A. This way, I can use this as a dropdown later on to select which logo I want to select.

I’ll create a named range called ‘Companies’ for these options. When using data validation, I’ll just enter the following as my list options:

Data validation list using the company names as options.

I’ll add this on to another sheet. My selection here will determine which image to pull.

Step 3: Creating another image for the lookup

I also need to create a picture that will pull the desired image. To do this, I can just copy any one of the images I inserted in the first step.

Picture lookup showing the company selected and the logo.

Step 4: Creating a named range for the selection

I’m going to create another named range, this time, I won’t be selecting a cell but I will go through the Formulas tab and select Name Manager where I’ll see all the named ranges I have set up thus far:

Name Manager in Excel.

Click on the New button. And here, I’ll need to use the INDIRECT function to reference the cell that contains the company value that was selected through the dropdown. In my example, that is cell H8. My named range, which I’ll call, ‘CompanySelected’ will look as follows:

Creating a named range in Excel.

Now, for the picture that is acting as your lookup, select it, and set the cell equal to the named range of ‘CompanySelected’ :

Assigning a named range to a picture.

I can adjust the size as large as necessary. And now, when I change my dropdown option, the image will automatically update:


If you liked this post on How to Do a Picture Lookup in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Evlookupmultiple1

How to Use VLOOKUP With Multiple Sheets

VLOOKUP is a popular function in Excel because of how powerful and easy it is to use. You can even use it to look up values on different sheets. And in this post, I’ll show you how you can do so dynamically so that you don’t always need to be adjusting your formula.

Why you might want to use multiple sheets in the first place

There are good reasons to use multiple sheets in your workbook. The first is that it makes it easier to organize your data. The second is that it can make your formulas more efficient. For example, running calculations on a tab where you have tens of thousands of rows would not be optimal and if you can split that up into smaller worksheets, you can make your formulas smaller in scope.

In my example, I’ve downloaded historical unemployment numbers by country. And rather than putting that data all into one sheet, I’ve created multiple tabs for countries. Not all of them, but just a few that I want to do lookups on:

Multiple tabs created for different countries.

Each tab is named after the country abbreviation in the data to make it easy to know what’s in each sheet. And inside each sheet is data that is formatted in the same way:

Historical unemployment data for the United States.

Creating the formula

If I just wanted to lookup the value for the United States’ unemployment rate from 1955, my formula would look as follows:

=VLOOKUP(1955,USA!D:E,2,FALSE)

I could replace 1955 with a cell reference. But other than that, this is in essence what the formula in its simplest form would look like. I’m looking up the USA tab as indicated by the ! symbol that comes after the sheet name. You don’t actually need to enter the ! mark. You can just type in the formula and then when you get to the lookup range, jump over to that tab and select your range — Excel will automatically add the exclamation mark for you.

While this formula works, it isn’t versatile. If I wanted to look up a different tab, I would need to change the reference, since it is hardcoded.

Making the formula dynamic

I have created named ranges for the country and year values:

What I want to be able to do is change any one of them and for my lookup formula to extract the correct value. The key to making this work is by including the INDIRECT function. With that, I can reference the specific range I need and use a dynamic tab name. Inside the INDIRECT function, I can concatenate the country value with the range:

INDIRECT(Country&”!D:E”)

But this on its own only specifies a range. I need to include it in the lookup formula for it to work:

=VLOOKUP(Year,INDIRECT(Country&”!D:E”),2,FALSE)

‘Year’ and ‘Country’ are the named ranges that I have used above. The key thing to remember is the exclamation mark that comes afterward and the range. By doing this, now I can change my formula to automatically pull from the correct tab while also looking up the year. It avoids me having to change the formula manually every time I want to use different tabs. It returns the same value as if I were to enter it myself:


If you liked this post on How to Use VLOOKUP With Multiple Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

vlookup1

3 Reasons You Should Still Use VLOOKUP

Many people will tell you that you should use INDEX/MATCH instead of VLOOKUP or that you should use a new function like XLOOKUP instead. But you shouldn’t be so quick to ditch arguably the most popular function on Excel as it’s still very useful. Below are just three reasons why VLOOKUP is still incredibly valuable:

1. It’s really quick to set up

If you’re using a combination of INDEX/MATCH, you’re going to have to use two functions, correctly set them up and nest one inside the other. Especially if you’re not used to it, it can take some time to set it up. Sure, it’s not like it’s going to take hours or even minutes to do, but if you need a quick lookup and VLOOKUP can do the job, why not just use it? Here’s how quickly it takes to set it up:

In the above example, I do a VLOOKUP in about five seconds. If you’re setting up INDEX/MATCH, you might still be trying to figure out which column to use for your MATCH argument. Being able to do VLOOKUP without almost thinking is what makes it such a great function, its speed is through the roof. Since you know the first column of your range is where you’re looking up values, it simplifies the process of selecting the columns and then you’re just counting how many columns over you’re extracting data from.

A couple of ways I expedited the formula above is by not typing out the entire function name (just entering VL and then tab to autocomplete the name), using 0/1 instead of typing out True/False and by not closing the last “)” as Excel will automatically do this for you.

Sure, it won’t work in all scenarios such as if you need to go left, that’s a well-known limitation of VLOOKUP. But as long as that’s not the case, there’s really no reason you need to bother with INDEX/MATCH when VLOOKUP will do the job. I’ve been using Excel for decades and I still love to use it when I can because it’s so easy to set up.

2. VLOOKUP is very versatile and will work on old versions of Excel

VLOOKUP may not be able to go left, but it can do wildcard searches and it can work if you need to pull the closest value — this is really useful if you’re dealing with tax brackets or anywhere that you’re looking for the closest value without going over (e.g. where you set the last argument to TRUE to look for approximate matches). While many people may use it strictly for exact matches, VLOOKUP is much more powerful.

And here again, using VLOOKUP in these situations is likely going to be no more difficult than the alternatives. While the temptation may be to use an exciting new function like XLOOKUP, the one big disadvantage is that it’s not available on older versions of Excel. With VLOOKUP, even if you’re working on a version that’s 20 years old you won’t have to worry about whether the formula will work.

3. Ease of use makes it ideal for training novice users and making templates with

Not only is VLOOKUP easy to set up, but it’s easy to understand compared to other, more complicated functions. If you’re making a template or need to train users, you don’t want to worry about them knowing complex formulas, especially when it involves nesting functions. Or telling them about a formula that may not work on their version of Excel. VLOOKUP’s also a good stepping stone for beginners to get them accustomed to how Excel formulas work.

Complex formulas are easy to break and harder for inexperienced users to fix. That’s why VLOOKUP’s ease of use is a key reason it’s worth using. If you’ve ever had to fix someone else’s formulas, you can definitely appreciate that keeping formulas as simple as they need to be can go a long way in making it easy to maintain and fix a spreadsheet.


If you liked this post on why you should still use VLOOKUP, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

excel-1771393_640

How to Use the New XLOOKUP Function

Forget using VLOOKUP or even INDEX and MATCH, Excel users can now use XLOOKUP!

Knowing how to use VLOOKUP has become almost a basic skill for Excel users. If you’re an intermediate or advanced user, you probably use INDEX & MATCH because of the limitations that are inherent with just using VLOOKUP. Since VLOOKUP can only return values to the right of what the value that you’ve found, it’s a less-than-optimal formula. You can either re-arrange your data, or you can use INDEX & MATCH. It’s a more flexible solution, but it’s also not ideal. After all, you’re now combining multiple Excel functions into one.

Enter: XLOOKUP

XLOOKUP is the solution that Excel users have been looking for…for decades. What the function does is allow you to do what was possible with INDEX & MATCH all in one simple formula.

Let’s go over it with some sample data on the world’s largest cities:

list of the largest cities in the world
Data courtesy of Wikipedia

Doing a regular lookup vs XLOOKUP

Here’s how my formulas would look like if I wanted to return the Country using a value from the City field:

vlookup and index match doing a regular lookup

In the above example, E5 refers to the capital city value. While the INDEX & MATCH combination works, it may not be the easiest for novice users who aren’t comfortable with nesting functions. Here’s how the same calculation would look using XLOOKUP:

xlookup doing a lookup

It’s a much simpler solution. The first argument takes the value you want to look for, followed by the range where you want to search for it, and then the range that you want to extract the corresponding value from. There’s no need to enter a column number the way you do with VLOOKUP, nor is there a need to add another function.

There are optional arguments you can use including how you want to match (see the next section). You can also choose the direction that the lookup goes, in case you don’t want to look in the same order as your data:

xlookup argument to search data

Using wildcards in XLOOKUP

Like with the other functions, you can also incorporate wildcards into XLOOKUP as well. Wildcards work similarly among all three formulas, but the key difference is that XLOOKUP has multiple arguments for its fourth (optional) argument which dictates how you want the data extracted. Entering ‘2’ will tell the function that you want to use a wildcard. Below are the options for the match_mode argument (optional):

  • 0: exact match
  • -1: exact match or next smaller item
  • 1: exact match or next larger item
  • 2: wildcard character match

Here’s a comparison of how you’d get the same result using all three functions using a wildcard:

using wildcards in xlookup vlookup and index match

The logic is the same in the sense that you’ll want to use a wildcard character like * around the term you’re trying to find a match of. In the above example, I used the * around the entire wildcard, and it returned the population for New Delhi in that example.

XLOOKUP here is actually a bit more complicated as with the other functions you didn’t need to specify that you were using a wildcard. Taking out the ‘2’ from the argument would result in XLOOKUP yielding an #N/A error. However, it could be that doing this will make it more efficient.

Finding the closest matches

One of the other options for the matches mentioned above were finding the next smaller or next larger matching items if an exact one wasn’t found. A good example of this is where you’re looking for something like a tax rate where you won’t find every possible income level that someone might enter and you need to ensure that it falls into the correct range.

Here are some sample categories:

sample tax categories and tiers

If I entered an amount of $17,000, it should put me in Tier 3, since that would be the threshold I would have reached under this hierarchy. Here’s an example of how this would be calculated in the three functions:

doing a lookup for tax brackets using xlookup vlookup and index match

All three formulas were able to return the same tier correctly, however, INDEX & MATCH is a bit more cumbersome again due to having multiple functions within it.

The advantage that XLOOKUP has here is that I can select the category that’s either directly below or above the amount I enter, effectively rounding up or down, simply by changing the fourth argument between a ‘1’ (exact or next largest item) to a ‘-1’ (exact or next smallest item).

This is not possible with VLOOKUP, and in order for this to be able to work with INDEX & MATCH, I’d have to change the order from ascending to descending. But what’s impressive is that XLOOKUP is able to find the correct category even if the values are not in any sort of order at all.

Have a look at what happens when I try to completely destroy any sort of hierarchy:

tax brackets sorted into tiers

This is an absolutely dreadful hierarchy that’s not consistent in any way possible. Do the formulas have any chance of getting it right? Here’s how the results looked:

xlookup index and match doing a lookup for next smallest category

Both the INDEX & MATCH as well as the XLOOKUP formulas were looking for the closest matches. INDEX & MATCH returned the lowest tier, which technically was incorrect since $17,000 came in higher than $10,000, which was Tier 2. And XLOOKUP, despite the mess of a hierarchy, was still able to pull out the correct group.

Ultimately, you never want to organize your data in such a horrible way, but this helps demonstrate just how strong XLOOKUP is, to be able to still come out with the correct calculation.

And just for fun, let’s flip the formulas around, this time looking for an exact match or the next largest category:

xlookup index and match doing a lookup for next largest category

There wasn’t a Tier 3 in my incomplete table, but XLOOKUP still found the next largest Tier which was at $25,000 – Tier 5. INDEX & Match found its way into Tier 7.

Creating a dynamic formula

One of the great things about INDEX & MATCH is that you can index an entire database and then dynamically change which column you want to extract from based on a selection and not have to update the range in the formula. For example:

creating a dynamic formula using index match

Why would you want to do this? The beauty of it is that you can change what value you extract based on your selection. Since you’re doing a match, it will look for that field and adjust the column accordingly using the OFFSET function:

You can do this in XLOOKUP as well, and here’s how that formula would compare to index and match:

xlookup index match doing dynamic formulas

The XLOOKUP formula is a bit more complicated as it needs two ranges, and thus, two OFFSET functions are needed. In the INDEX & MATCH combination, only one OFFSET function is needed as it only requires a column number for one of its arguments. Either way, you still need to be familiar with using OFFSET so it’s probably not a dealbreaker if XLOOKUP is a bit longer.

Great, so how do I get XLOOKUP?

There are two things you need to be able to get access to XLOOKUP:

  • Office 365
  • Enrollment in the Office 365 Insiders Program

It’s not an exclusive club or anything, all you have to do is to follow the steps outlined here. By selecting the ‘Insider’ option rather than Monthly, you’ll get more frequent updates and changes. Once you’ve got it set up, then it’s just a matter of waiting for the updates to roll out to you. There’s, unfortunately, no notification, I’d just suggest checking every now and then to see if XLOOKUP shows up in your functions list.

Caveat

One of the things you should remember, however, is that while it may be great to use XLOOKUP, old versions of Excel won’t have access to this flashy new function. And so it’s important to still be familiar with using VLOOKUP and INDEX and MATCH.


If you liked this post on How to Use the New XLOOKUP Function, please give the site a like on Facebook. Also be sure to check out our templates section. You can also follow us on Twitter and YouTube.

addina

Free Excel Add-In: 20+ Macros to Automate Tasks and Make You More Efficient

 
 
 
 

This add-in is completely free and includes over 20 macros that I have worked on myself and that I hope will help you. Any feedback is welcome, as well as any suggestions for other macros you would like to see added.

Disclaimer
These macros have not been tested exhaustively so I don’t offer any guarantees that they will work under every possible scenario. However, if you run into any issues please let me know and I will work to correct them. When using macros you should always save your work before executing them, as there is no undo button if something doesn’t go as expected.

If you understand and accept these risks, please feel free to download the file here

Below is an overview of all the different macros in the file.

Toggling Workbook and Worksheet Calculations

For those that work on large spreadsheets, this can make it easy for you to not only turn off and on calculations for a workbook, but for individual worksheets. It will also allow you to see whether or not they are set to on or off.

One of the things people sometimes don’t realize is if you turn off calculations in Excel at the workbook level, that disables it for all other workbooks. The danger is if you switch to another file you’re working on you may not realize calculations are still off, by seeing the toggle and whether it is set to on or off can help prevent that.

If you only need an individual worksheet to be off, you can do that as well. However, note that if the workbook calculations are set to off, then all the worksheet calculations will be off as well, regardless of whether or not they say on or off. Workbook settings will supersede any worksheet settings.

Very Hidden Tabs

 

Hiding tabs in Excel may seem pointless since even an average user would know that you can right-click and select un-hide. However, not many know that you can set them to be ‘very hidden’ and where right-clicking won’t do anything.

I’ve covered this in a post before here, and in this add-in I’ve made it so that you can easily both hide and unhide very hidden tabs.

 

Removing Excess Spaces

 

This macro will delete any trailing, leading, or extra spaces in a cell and will help to clean up your data.

Converting Formulas to Values

In some instances you may want to get rid of your formulas and replace them with their results (values), this macro will do that for you. Just select the cells and click the button and the formulas will be gone.

Converting Numbers to/from Text and Changing Signs

These buttons will allow you to choose whether you want to convert numbers that are stored as text into numbers, switch numbers back into text, or just flip the signs from positive to negative or vice versa.

Filtering Out Zero Values From Tables

 

If you’ve got a table or pivot table that has a lot of zero values in it that you don’t want to see, this will filter them out. This won’t get rid of errors, just zeros, and for it to work in a table, it assumes that the table will start in column A, otherwise it won’t filter the right column for you. The zero values will be removed from the column where your active cell is, so you have to make sure you’ve got the right cell selected before clicking this button.


















Multiplying and Dividing by a Factor of 1,000

This is pretty straightforward and is mainly here since dividing can be helpful if you’re dealing with financials and want to cut down the number of placeholders. Multiplying will simply undo those changes.

Combining Columns

If you have data across multiple columns and want to combine it, you can do that with this macro. You don’t have to select entire columns, it can just be a selection. The columns don’t even have to be right next to one another.

 

Cycling Through Errors

 

If you want to find all the errors on the worksheet you’re on, this macro will cycle through all of them for you. You can correct an error, and click the Next button to go onto the next error in the sheet.


Removing Merged Cells

When you’re trying to do data analysis, merged cells can be a nightmare, and this will unmerge the cells and put the value into each of the cells as well.

 

Protecting Your Data


This will help convert your sensitive data into a random number preceded by a series of X’s. There’s a post here detailing how that process works.

 

Filtering Pivot Tables

If you’ve got a pivot table and want to select multiple items, it can be a tedious process. This macro will allow you to select what selections you want to filter by and apply them for you. But the first cell in the selection needs to match the field name in the pivot table.

 

Adjusting the Default Pivot Table Format

One of the more annoying things in Excel is that when you create a pivot table, it defaults to a format that isn’t very useful. This is what the macro will help you do:

 

Quickly Formatting Pivot Table Fields

If you’ve ever needed to change how fields are formatted in a pivot table you know that simply selecting the column and changing the format is a temporary fix. You need to actually go into the field settings. This macro will do that for you, and will set the settings to either comma or accounting format.

Quickly Extracting Unique Values

 

There are plenty of ways you can get unique values, but I thought an even easier way would be to select the cells and specify where you want those unique values to be output.

Counting Unique Values

If you just want to quickly count how many unique values are in your selection, this macro will do that for you.

Do a Reverse Lookup

Everyone knows how to do a VLOOKUP, but doing the reverse is another story. Take for example a credit card statement. You could have a lot of detail in the string, but only a certain few characters relate to the actual vendor or detail you want:

Using this lookup function the cells you select will be compared against a list you have specified, and if there are any matches, the corresponding field will be returned:

 

The result:



If you’re doing this with a lot of cells and have a big list, it could be time consuming, and that’s why I added a progress bar to this macro.

Comparing Sheets

This macro essentially looks at two sheets and tells you what is different, and will highlight the differences in them.

 

Updating Links

If you want to update the link for a cell, it’s not an easy process and involves you right-clicking on the cell and putting the link in there. This macro will do that step, and for the link it will put the cell’s value there, so if you put in the url you want in the cell and then run the macro on those cells, the links will be updated.

Adding the Location to the Footer

Clicking this button will add the path to the workbook you’re working on into the footer so when you
print it out it’s easy to see where the file is saved.