Queries, VLookup, XLookup & Co.: Find information and match values in Excel 365 and 2021

By Ina Koys

Excel can take large data sets. But getting hold of required data can be tricky. Here, different possibilities are explained: The filter and the VLOOKUP() which are often heard of, but not really mastered. But there are more options like the Advanced filter, IF(), Database functions and the new, cutting-edge ones like SWITCH() and the brand new,

• Language: English
• Publisher: Computertrainerin.de
• Release date: May 2, 2023
• ISBN: 9783910233126

Ina Koys

Ina Koys is an experienced instructor for Microsoft Office. Many questions are frequently asked in trainings, but seldom covered in books. Now she answers some of them in her originally German "short & spicy" series. A little accent will add to the fun :-)

Book preview

Excel worksheets often contain huge datasets. All important information is somewhere within them; finding it is a whole different thing. And once it’s found it may need some processing.

Therefore, we’ll be covering known, less known, and brand-new ways of filtering, evaluation with database functions and a number of ways to query, applying custom conditions.

To get it done, there’s a set of different possibilities, traditional ones and also the cutting-edge ones, so far confined to the users of Excel 365.

The example data for this book can be downloaded from

www.ShortAndSpicy.online

Have fun finding new insights!

The filter is a well-known and frequently used way to find information in lists and tables. But often, the full range of possibilities isn’t maxed out by far. So, for a beginning, let’s have a look at what may have remained unexplored yet. To get it done, I brushed up a little list using Format as Table - an often-underestimated feature. I here applied it to a little list by clicking into it and then selecting a format of choice from the Home tab.

I herewith applied much more than just colours. Read more about it in Roll away the boring stuff!, vol. 7 of this series. Here, I won’t therefore not go into each and every detail; we will later return to the table formattings for a moment. Anyway, after clicking the respective template, the example looks like this:

We here have a little line-up of names (from the point of Excel: texts), numbers and dates. This one is pretty clear. But given the million rows Excel can hold, we clearly would have to do something.

We now want to find out, wheather the name Taylor appears in the list. To get it done, it absolutely does to open the dropdown in the Name column and to start typing. If there are entries containing the character string, they will be offered; even if it’s found somewhere in the middle of the word.

If you’re looking for something more specific, check out the menu item Text Filters, that will always guide you to the Custom Filter. Still, it provides some convenient shortcuts.

Now, we’d like to find all names beginning with C and ending with r. Clicking on Begins With… we reach the same dialogue we’d always use, only with