Excel Functions INDEX and XMATCH

One of Excel most useful functions that is mostly used to look up values is INDEX and XMATCH. Although they are separate functions but they are sisters.

=INDEX (array, row_num, [col_num], [area_num])

=XMATCH (lookup_value, lookup_array, return_array, [match_mode], [search_mode])

XMATCH returns a number of cell order where the look up value. INDEX on the other hand returns value of a given number of an array. So XMATCH feed INDEX and you get your look up. Remember this, the array in INDEX is your answer range. It can perform more complex functions than XLOOKUP. or does it 🤔. An example for that can be shown:

Tell me if it works for you. Or it doesn’t 😅. Let’s discuss.

Don’t Ask What If? Ask What IFS?

One new super useful function of excel is IFS 💪🏻. Yes, no more hideous nested IF 🤢. And it goes like this:

=IFS(Condition1, Value if True1, Condition2, Value if True2, Condition3, Value if True3, …)

Quite self explanatory really! but there is the catch 🙄! it only tests if conditions are true “what if” you have a condition not specified ones! you’ll get the #N/A right into your face.

Don’t you worry I got you covered. Add a final condition that is always true as following

… , 1 , Value if else)

Note: True = 1 , False = 0

Thats it 😊. And here a bonus, now Excel has maxifs and minifs function like the lovely sumifs and countifs of before 😎.

Excel To The Next Level (Macros)

VBA (Visual Basic for Application) macros can take your Excel work to next level. Imagine you have a raw data with some variations on the input like, extra spaces, useless number within text and it’s provided to you in text file. You need to clean it up and do your calculation on it. EVERY … SINGLE … DAY😅! Macros can do this monkey job 🐵 for you.

But first! you need to add the developer ribbon tab. in Mac (Excel>Preferences>Ribbon and Tool Bar) in windows (File>Options>Excel Options>Customize Ribbon)

The easiest way to learn is by starting recording a macro! You click record, do the job you want to do, then stop recording.

Edit it by clicking on the macros icon.

Change parameters here and there. Improve your script gradually by searching the web. I would recommend sites for you like stackoverflow but why limit yourself! Google it! Whatever job you have in mind, chances are someone already has done it before 😁.

One more thing, When saving the file, you are going to receive a warning 😅😣

You got to save the file in macro-enabled format, which is (.xlsm) .

Fiddle with it and enjoy.

Universal Office Tip That Will Increase Your Productivity

This one is simple and quick. You just need to know it once and it is about how to find any functionality in Microsoft office application? In two steps

• First, look at the ribbon tab titles and guess under which title does the function you are looking for fall?
• Second, look at the subtitles at the bottom of that tab. Viola that’s it.

Lets take and example, in Excel where do you find Define Range Name? 1) My guess it’s either under data tab or formula. 2) In data tab I have subtitles (Get External Data, Connection, Data Types, Sort and Filter, Data Tools, Forecast, Outline), I find it doesn’t fall under any of those subtitles, then I go to formula tab and I found Define Names as subtitle right infront of my face 😅.

Excel New Dynamic Array

Ok guess that huge! This going to change Excel as we know it. It’s called Excel Dynamic array, which is a new way Excel processes formula. So what is it?

Simply now you can get an output as multiple cells that would “spill” out from one cell. Like of you have 1000 record of cities and want to know unique values. Just in one single shot. Here you go:

= UNIQUE(B2:B44)

Viola that’s it.

Moreover there is a function called SORT, so you can sort this output.

= SORT(UNIQUE(B2:B44))

However, you need to be carefull that below cells are empty because the array will “SPILL” into it.

If not you’ll get a #SPILL! error

Now, Are you thinking what I am thinking? Yes Dynamic lists 😍. But this for another day 🙄

Excel Pivot Table

If you don’t know it, well!, You gotta 😁. It’s one of those things you just need to know. Simply it works by highlighting your table then go to Insert > Pivot Table.

Now you’ll see on the right pane 4 rectangles below the list of you table heading. You basically drag and drop the columns and rows titles at first . As for the value part you can add any title that has values in all rows for counting purpose. There are different functions you can use ranging from counting to summing. Fiddle with it and and I’m sure you’ll get the hang of it.

Excel – Filters on Multiple Tables in One Sheet

If you are an Excel head and you use filters in your table to lookup data, you might get upset because you only can have one table per sheet to filter. Well not anymore! If you define your tables, well, as tables then you can have more than one table with perk of filter for each table in one sheet.

Tip: remove table style to maintain your styling 

and here you go …

Find it in Excel with XLOOKUP Function

One of the new function that I wish it existed before is XLOOKUP which is a replacement for VLOOKUP. 

=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

• lookup – The lookup value.
• lookup_array – The array or range to search.
• return_array – The array or range to return.
• not_found – [optional] Value to return if no match found.
• match_mode – [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
• search_mode – [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

• 

Benefits over VLOOKUP,

• Look up and return columns assigned by address. So when you insert or remove columns/rows, no need to change formula
• Value of not found just saves an extra iferror statement