Monthly Archives: May 2021

Functions

XLOOKUP

Recently I have read quite often about XLOOKUP function. Once, after an update, I also got the function in my Excel, I wanted to test the function by myself.

We have a simple sales report. Months are on horizontal axis and product on vertical.

First, we repeat traditional VLOOKUP.

VLOOKUP consists of four arguments.

  1. Lookup value: which value to look after, in this case it is P4 in the cell B10.
  2. Table array: where to look after the look up value, B3:E6.
  3. Column index number: how many steps from B-column you have to take to enter the column where the look up value is, the B column is one. We need to take four steps to check the March values.
  4. Range lookup: are we looking after an exact match (false) or a rough match (true). I normally use false just like this time.

If you want that VLOOKUP returns an array, you can add an extra value on top of the return array, like C9 to E9.

If the VLOOKUP does not find an exact match for the lookup value, the formula return #N/A.

This can be tackled with IF and ISERROR functions like:

=IF(ISERROR(VLOOKUP($B10;$B$3:$E$6;E$9;FALSE));”Not found”;(VLOOKUP($B10;$B$3:$E$6;E$9;FALSE)))

Now it is time to introduce our new friend XLOOKUP.

Now it is time to introduce our new friend XLOOKUP.

When you enter the formula, just activate the cell C9.

The arguments for XLOOKUP are:

  1. Lookup value: which value to look after, in this case it is P4 in the cell B9. This is similar to the VLOOKUP.
  2. Lookup array: list of values containing the lookup value, $B$3:$B$6.
  3. Return array: array of the values, in our case sales values, $C$3:$E$6.
  4. If not found: if the formula cannot return any value, the formula return this value. “not found.
  5. Match code: are we looking after an exact match or a rough match. I use 0 to have an exact match. This is same kind of as range lookup in VLOOKUP.

P5 cannot be found, so the result is “not found”.

When match code is -1, the XLOOKUP returns next smaller value.

Another good thing with XLOOKUP is that it works also vertically, just like HLOOKUP.

Another good thing with XLOOKUP is that as the function returns an array, you can frame with SUM function to get the sum of a row.

XLOOKUP returns an array which is advancement compared to VLOOKUP and HLOOKUP. XLOOKUP makes things easier.

Excel

Email generator

You have list of names, like Firstname Lastname. The email addition is comp.com. So the email should be Firstname.Lastname@comp.com.

This is quite simple. In A-column you have names and in C-column the company extensions.

The formula in E1 is =LEFT(A1;(FIND(” “;A1))-1) & “.” & (RIGHT(A1;LEN(A1)-(FIND(” “;A1)))) & “@” & C1 .

First, we take from left digits till space minus one, so the first name is captured. We take four digits from left as the space is the 5th digit. Then dot is added. After that we take digits from right number of digits equal to length of the A1 minus the place of the space. Number of digits in A1 is ten digits. The space is the 5th digit. We take five digits from right. Then we add @ sign and organization information.

The issue is more complex if a family of two persons are in the same line.

If there is Firstname & Firstname1 Lastname and the extension example.com, then the Excel should return Firstname.Lastname@example.com; Firstname1.Lastname@example.com . Then you can copy paste both the emails to Outlook. Sometimes there are two names, sometimes just one.

Like this.

The script in E1 is:

=IF(ISERROR(FIND(“&”;A1));(LEFT(A1;(FIND(” “;A1))-1)&”.”&(RIGHT(A1;LEN(A1)-(FIND(” “;A1))))&”@”&C1);(LEFT(A1;(FIND(” “;A1)-1))&”.”&MID(A1;(FIND(” “;A1;(FIND(” “;A1;FIND(” “;A1)+1))+1))+1;LEN(A1)-(FIND(” “;A1;(FIND(” “;A1;FIND(” “;A1)+1))+1)))&”@”&(C1)&”;”&(MID(A1;(FIND(” “;A1))+2;((FIND(” “;A1;(FIND(” “;A1;FIND(” “;A1)+1))+1))-(FIND(” “;A1))-2)))&”.”&MID(A1;(FIND(” “;A1;(FIND(” “;A1;FIND(” “;A1)+1))+1))+1;LEN(A1)-(FIND(” “;A1;(FIND(” “;A1;FIND(” “;A1)+1))+1)))&”@”&(C1)))

The script got that complex, that I was happy when it was working. So, I did not analyze the script.