Queries, VLookup, XLookup, & Co. E-Book

Ina Koys

• Herausgeber: Computertrainerin.de
• Kategorie: Fachliteratur
• Sprache: Englisch
• Veröffentlichungsjahr: 2023

Beschreibung

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, very convenient XLOOKUP(). All are explained with examples and their specific benefits and downsides.

Leseprobe

Queries, VLookup, XLookup & Co.

Find information and match values in Excel 365 and 2021

Ina Koys

Short & Spicy, vol. 11

Contents

0      What we’re going to look at

1      The standard filter

2      Advanced Filter

3      The FILTER()-Function

4      Database functions

5      Insertions using conditions

5.1      IF()

5.2      IFS()

5.3      SWITCH()

6      Calculations using conditions

7      VLOOKUP()

8      XLOOKUP()

9      More

0 What we’re going to look at

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!

1 The standard filter

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 the pre-defined setting of begins with. We need to pick our second wish from the lower list and of course insert the desired letters.

Now clicking OK only Cooper will still appear in the table. And as the filter settings are not always obvious, we now have a changed symbol that reports its details on hovering with the mouse pointer.

---ENDE DER LESEPROBE---