Google Sheets offers a wide array of functions that can make data management a breeze. Among these, the XLOOKUP and SMALL functions stand out as essential tools for anyone working with datasets. In this blog post, we’ll explore how to use these two powerful functions together in Google Sheets, showing you a practical example that you can start using today.
Let’s dive right in, and by the end, you’ll be able to quickly find and display the smallest values in your dataset, paired with corresponding products or items!
Understanding the Data
For this example, let’s work with a small dataset that contains three columns: S. No., Product, and Price. Here’s what the data looks like:
Our goal here is to use XLOOKUP and SMALL to find the bottom three products with the lowest prices. Sounds fun, right? Let’s go!
The Formula: Breaking It Down
To get the desired result, we’ll use the following formula:
=XLOOKUP(SMALL($C$4:$C$20,E4), $C$4:$C$20, $B$4:$B$20)
This might look a bit intimidating, but don’t worry, I’ll break it down step-by-step:
- SMALL($C$4:$C$20, E4): The SMALL function identifies the smallest value in the range C4
(which is our list of prices). The second argument (E4) tells SM ALL which “smallest” value to pick. For example, when E4 = 1, it picks the smallest value; when E4 = 2, it picks the second smallest, and so on.
XLOOKUP(SMALL($C$4:$C$20, E4), $C$4:$C$20, $B$4:$B$20): Once SMALL has found the smallest price, XLOOKUP steps in to locate that price in the range C4
and return the corresponding product from the range B4.
In simple terms, we are first identifying the smallest prices, then using XLOOKUP to find the products associated with those prices.
Output of the Formula
Using the formula, we can easily find the bottom three products in terms of price. Here’s the output:
Pretty cool, right? With just one formula, we’ve extracted valuable insights from our dataset.
Why Use XLOOKUP and SMALL Together?
Now you might be wondering, why use these two functions in tandem? Here are some advantages:
- Efficient Data Lookup: The SMALL function helps you quickly identify the smallest values in a large dataset, and XLOOKUP pairs those values with relevant details like product names or categories.
- Dynamic Results: As your data changes, these formulas will automatically update, giving you the latest insights without needing to manually adjust anything.
- Simplicity: Once you get the hang of using these functions, they make your data analysis so much easier and faster.
Final Thoughts
The combination of XLOOKUP and SMALL functions in Google Sheets is an incredibly powerful way to look up specific data points in your spreadsheets. Whether you’re managing pricing data, sales information, or any other kind of dataset, this dynamic duo will help you extract key insights with ease.
Visit our YouTube channel to learn step-by-step video tutorials
Youtube.com/@NeotechNavigators
