Text Lookup

We will begin by using xlookup to look up text, very much like vlookup does.

Below is a partial screen shot of our data from the worksheet “Text Lookup” showing general economic situation of various parts of the world.

Question: What is the price to income ratio of Australia?

Answer: =XLOOKUP(“Australia”,’Text Lookup’!A:A,’Text Lookup’!B:B,,0,1)

Here are the parameters of Xlookup:

XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

Since the question asks for price to income ratio, the return array would be column B. We are happy to have the function return an error if the value “Australia” is not found in the list for some reason, and so leave the if_not_found parameter blank.

Match mode has the following values:

In this case an exact match (0) is what we are after. We will look into the data to see if there are other spellings or variations of the name “Australia” should an error be thrown.

The last parameter, search mode, has the following values:

The first option, searching first-to-last, is equivalent to searching the list from the top down. As we believe that the list contains unique names of countries, this is perfect for what we need.

In fact, since the default for match mode is 0 and for search mode is 1, we can abbreviate the formula to be

=XLOOKUP(“Australia”,’Text Lookup’!A:A,’Text Lookup’!B:B)

Note that like its vlookup cousin, xlookup is not case dependent. So you can have the search value spelled as “ausTRaLia” or whatever variation, and it would not affect the search result.

Date Lookup

Looking up dates require the use of date-related functions. Let’s say we want to know the order number for the order placed on 9th April. Based on the data below, we would need to do a date lookup. Whereas vlookup function would have difficulty looking up columns to the left of the reference column, xlookup function does not have that restriction.

In formula bar, put

=XLOOKUP(DATEVALUE(“9/4/2020”),B2:B14,A2:A14)

This would give us X790 as the answer.

For questions such as the number of orders in April, refer to CountIf function.

For questions such as total sales in the first week of April, refer to SumIf function.

Horizontal Lookup

Xlookup can replace the traditional Hlookup as well. Suppose we have the data below:

Question: What is the number of downloads for App D?

Answer: =XLOOKUP(“App D”, B1:M1,B4:M4)

Again, since we only want an exact match, we can ignore optional parameters.

Dynamic Lookup

Xlookup is one of those functions that can return a range, among similar range-returning functions such as Index, Offset, Indirect etc.

We will use this property perform a cross lookup of both vertical and horizontal column to find the intersection point.

In cell G7, we would type =XLOOKUP($F$7, $A$2:$A$27,XLOOKUP(G$6,$B$1:$D$1,$B$2:$D$27,,0,1),,0,1).

The first part of this formula again look up the value in column A, the vertical list of apps. The nested xlookup function takes into account column heading in G6, and look up that heading in the original data headings from B1 to D1. The return array is what is different here. It spans across columns B to D and only return the column that matches G6.

Note that we do not put a dollar sign in front of G in the second xlookup. This is because we want to keep it dynamic, so that when the formula is dragged across to the other cells H7 and I7, it will not break, but instead changes accordingly.

Let’s turn to other variations of xlookup.

Look Up Last Value

Companies keep a record of client contact and frequently need to know the last contact date.

Note that for each contact type, same agent could have liased on different dates.

In cell E2, type the formula

=XLOOKUP(A2&B2,$A$2:$A$14&$B$2:$B$14,$C$2:$C$14,””,0,-1)

This formula only works because Contact Date in column C is sorted in descending order from oldest to newest. If it is sorted the other way, then we need to change the last parameter to 1, which is searching first to last.

The result would look like this:

Category Lookup

Category lookup, or lookup to find the nearest match, is useful when we want to see whether a value fits into a certain category, or basket. It is frequently applied to calculate tax bracket in UK and Australia.

From the following data, what is the grade for each subject based on the score?

Here we want to calculate the grade for each subject and put the answer into cells C4 to C7.

Cells A10 to B15 is the reference table.

If we put this formula in cell C4:

=XLOOKUP(B4,$B$11:$B$15,$A$11:$A$15,,-1,1)

and fill down the rows to cell C7, we would have the following result:

Wildcard Lookup

Using wildcard allows us to specify a pattern instead of an exact string. Frequently overlooked in the vlookup age, it is nonetheless a useful repertoire. The ? mark specifies the position of exactly one character, while the * mark represents the position of multiple characters.

Question 1: What is Coca-Cola’s Total Company Sales in 2013?

Question 2: What is Hershey’s 2013 Net Income?

In cell K7 put this formula

=XLOOKUP(“Coca-Cola*”, $C$3:$C$22,$F$3:$F$22,,2,1)

We need to specify a match mode of 2 for wildcard, otherwise the function would not work as expected. We can certainly use “*Coca-Cola*” if we suspect that it is possible for some words to precede its usual name.

For Hershey, the solution is pretty much the same.

=XLOOKUP(“Hershey*”, $C$3:$C$22,$G$3:$G$22,,2,1)

Multiconditional Lookup

With Index Column

This is the part where we have to combine multiple conditions. We firstly explore the option of using an index column. An index column is a type of helper column. Essentially it performs an intermediate calculation that allows us to get the job done.

Question: Write a Xlookup function to look for the stock level for North Sydney branch, L1, Promotion as Yes

 

We create an index column in this case by combining variables Branch, Product and Promotion (columns A to C). In cell A12, we would put =B12&C12&D12

Then in cell B18 where answer should be given, we would put

=XLOOKUP(A12,$A$12:$A$16,$E$12:$E$16,0,0,1)

Notice in this case that we specified the if_not_found parameter as 0, which means instead of an #N/A error, we want it to show zero stock if not all criteria are met.

In reality, instead of putting A12 as the search value, we would construct the string ourselves to be

“North SydneyL1Yes”

The index column can be hidden once xlookup is performed. Alternatively copy the values elsewhere.

Without Index Column

We explore another example below without the help of an Index column.

Question: What is the number of employees for the microsoft in Optical industry?

 

In this case there are two criteria: “Microsoft” and “Optical”.  It is easier just to use Index Match. A2:A9=”microsoft” gives an array of True and False. So does the second conditional statement.

When multiplied, True is regarded as 1 and False as 0, and if any one of the condition is false, the result would evalute to 0.

The data has been constructed so that only one result matches.

Array Formula

While Microsoft is moving away from Ctrl+Shift+Enter array formula, it is still doable at the moment of writing. In this case, the old friend Vlookup is called upon to complete the task. The method above of using Index Match can be applied as easily by adding in the third condition into the formula.

Other Uses of Xlookup

Create Dynamic Range

Traditionally Index Match or Offset function is used to calculate the range from the top of a column to the last cell in the column as row records grow. Now we can use the lookup ability of Xlookup to create value-based dynamic range instead of position-based.

 
    Suppose we have the partial screenshot of the following data in columns A to C.  We can use Advanced Filter in range E1 to H2 to filter for the records we desire.

    However, the method requires the user to select advanced filter everytime for a new criteria.

    Instead, a dynamic range named “PriceRange” can be defined that matches the criteria. =XLOOKUP(‘Value Based Dynamic Range’!$F$13,’Value Based Dynamic Range’!$C$2:$C$27,’Value Based Dynamic Range’!$C$2:$C$27,,1,1):XLOOKUP(‘Value Based Dynamic Range’!$F$14,’Value Based Dynamic Range’!$C$2:$C$27,’Value Based Dynamic Range’!$C$2:$C$27,,-1,-1)

 

While the formula looks extremely long, it is simply combining the range from two separate Xlookup functions. The first one looking for the minimum value in cell F13 and searching from first to last with an exact or next larger item. The second one looks for the maximum value in cell F14 and searching from bottom up for the next smaller value. The price list is sorted from smallest to largest.

Price Filter  
Min Price 5
Max Price 10

We then put this formula in cell J16

=OFFSET(PriceRange,0,-2,COUNTA(PriceRange),3)

Since PriceRange is a dynamic range, it will respond to the values we put in Min Price and Max Price.
The Offset function widens the originally one-columned PriceRange to include columns to the left, hence -2 (two columns to the left).

This way, data can be filtered in real time.

Google Sheet Equivalent

Recommend using Filter function as a great equivalent. Unlike Query function, it does not require you understanding SQL syntax.

=FILTER(return_array, lookup_array=lookup_value)

It has the added advantage of performing the equivalent of multiconditional Index Match lookup as mentioned above without needing to learn the more complex-looking Index Match syntax.

Pin It on Pinterest

Share This