Excel XLOOKUP with Multiple Criteria - Multiple Lookup Arrays (Multiple Columns)

Summary

In this video, the presenter demonstrates how to use the XLOOKUP function with multiple criteria to return specific values from a dataset. The example focuses on retrieving Bob's sales in the northern region, using logical tests to match both the name and the region. By leveraging TRUE and FALSE values in conjunction with multiplication, the formula identifies the correct match, allowing XLOOKUP to return the appropriate sales value. The video concludes with a simple explanation of how the formula works and encourages viewers to like and subscribe for more tips.

Takeaways

• 😀 The goal is to use XLOOKUP with multiple criteria, such as finding Bob's sales value in the northern region.
• 😀 XLOOKUP can be tricky when working with multiple criteria, but it can be solved by combining TRUE/FALSE tests.
• 😀 TRUE is equal to 1, and FALSE is equal to 0, which is essential for performing logical tests in Excel.
• 😀 Multiplying TRUE (1) and FALSE (0) values results in 0, while multiplying two TRUE values (1 * 1) results in 1.
• 😀 The first step is to test if names in the dataset are equal to 'Bob', which produces a set of TRUE/FALSE values.
• 😀 The second test checks if the region is equal to the desired region (e.g., northern), also producing TRUE/FALSE values.
• 😀 Multiplying the two sets of TRUE/FALSE values gives a new array, where the result is 1 only when both tests are TRUE.
• 😀 The position of the 1 in the new array corresponds to the row where both conditions are met (Bob in the northern region).
• 😀 The final step is using XLOOKUP to look up the position of the 1, and return the corresponding sales value from the sales column.
• 😀 By combining these two logical tests, you can successfully use XLOOKUP with multiple criteria to return the correct value.
• 😀 The video concludes with a reminder to like and subscribe if the tutorial was helpful, with a preview for future videos.

Q & A

What is the purpose of using XLOOKUP in this video?

The purpose is to return Bob's sales value from the northern region, despite Bob appearing twice—once for the southern region and once for the northern region.

How does the script explain the concept of TRUE and FALSE values?

The script explains that TRUE is equal to 1 and FALSE is equal to 0. When multiplied, TRUE * FALSE results in 0, and only TRUE * TRUE gives 1.

What test does the video perform first in the XLOOKUP calculation?

The first test checks whether the names in the dataset are equal to 'Bob'. The result is a series of TRUE/FALSE values corresponding to each name.

How are TRUE and FALSE values used in the XLOOKUP formula?

The TRUE/FALSE values are multiplied between two criteria tests: one for the name ('Bob') and another for the region. Only when both tests return TRUE (i.e., position 4 in the example) does the XLOOKUP return the desired value.

Why is the calculation wrapped in brackets before being multiplied?

The brackets ensure that each test (name and region) is evaluated as a distinct logical expression before the multiplication operation is performed.

What is the significance of position 4 in the formula?

Position 4 is where both criteria (name = 'Bob' and region = 'Northern') return TRUE. This is the key location for the XLOOKUP to fetch the sales value from.

What is the lookup array in this XLOOKUP formula?

The lookup array is the result of the multiplication of the TRUE/FALSE tests for both name and region, which returns a series of 1s and 0s.

What does the return array represent in this formula?

The return array represents the sales values. The XLOOKUP returns the value corresponding to the position where the lookup array is 1.

How does XLOOKUP find the correct sales value?

XLOOKUP finds the correct sales value by using the position returned by the multiplication of TRUE/FALSE values (position 4 in this case). It looks up that position in the sales values array.

What does the speaker ask viewers to do at the end of the video?

At the end of the video, the speaker asks viewers to give a thumbs up and subscribe to the channel if they found the content useful.