AMAZING EXCEL
FUCTION GUIDE
XLOOKUP()
FILTER() &
UNIQUE()
Jordan Goldmeier
36k Followers
XLOOKUP() Function
Definition
XLOOKUP is an Excel function used for
dynamic lookups across both vertical
and horizontal ranges.
Purpose
Look up values in range or array.
Syntax
=XLOOKUP(lookup_value, lookup_array,
return_array, [if_not_found],
[match_mode], [search_mode])
JORDAN GOLDMEIER
Parameters
lookup
Value to search for
lookup_array
Range to search within
return_array
Range to return matching value from
not_found
Value if no match is found
match_mode
0 (default) = exact
1 = next largest
1 = exact match or next larger
2 = wildcard match
search_mode
1 (default) = first to last, -1 = reverse
JORDAN GOLDMEIER
Basic Use
The XLOOKUP function can be used to
get the price of a particular product.
=XLOOKUP(E3, B3:B12, C3:C12)
Figure 1 Getting the price of a product
JORDAN GOLDMEIER
Lookup From Anywhere
Unlike VLOOKUP, you can XLOOKUP
from anywhere and still get the
desired result.
=XLOOKUP(E3, C3:C12, B3:B12)
Figure 2 Getting the price of a product
JORDAN GOLDMEIER
Approximate Match
Getting the Prices when they are
dependent on the Product Quantity
you purchase.
=XLOOKUP(E3, B3:B7, C3:C7, , -1)
Figure 3 Getting the prices based on quantity
JORDAN GOLDMEIER
Two-Column Lookup
You can get Sales Value for a Sales
Representative and a specific Month if
the Months are in columns.
=XLOOKUP(
C2,
B6:B12,
XLOOKUP(C3, C5:E5, C6:E12)
)
Figure 4 Getting the sales value from pivot data
JORDAN GOLDMEIER
Lookup From Last
You can also get the latest prices in
case your prices are being updated
regularly.
=XLOOKUP(F3, B3:B8, D3:D8, , , -1)
Figure 5 Getting the latest prices
JORDAN GOLDMEIER
Multiple Criteria
You can even get the prices for a
product with multiple categories.
=XLOOKUP(
1,
(B3:B9 = F3) * (C3:C9 = F6),
D3:D9
)
Figure 6 Getting the price for a multi-category item
JORDAN GOLDMEIER
Total a Whole Row
You can also get the totals for a Sales
Representative if the Months are in
columns.
=SUM(XLOOKUP(C3, B6:B12, C6:E12))
Figure 7 Getting the totals of a sales rep
JORDAN GOLDMEIER
FILTER () Function
Definition
The FILTER function in Excel allows you
to extract specific data from a range
based on given criteria
Purpose
Filters a range with given criteria
Syntax
=FILTER(array, include, [if_empty])
JORDAN GOLDMEIER
Basic Use
With the FILTER function, you can extract
data from a specific region
=FILTER(B3:E8, D3:D8 = "East")
Figure 1 Getting the details of the East Region
JORDAN GOLDMEIER
Multi-Criteria AND
When working with multiple conditions
with an AND condition, like a region with
sales above a certain amount, FILTER with
“*” (asterisk) operator can be used
=FILTER(B3:E8, (D3:D8 = "East") * (E3:E8 > 100))
Figure 2 Filtering orders by region and amount
JORDAN GOLDMEIER
Multi-Criteria OR
Using FILTER with “+” (plus) operator
shows data that meets at least one
condition, like high sales OR low stock
=FILTER(B3:D8, (C3:C8 > 200) + (D3:D8 < 10))
Figure 3 Getting high sales or low stock details
JORDAN GOLDMEIER
Multiple Matches
Adding COUNTIFS to FILTER lets you pull
data for specific customers, showing only
the details that match
=FILTER(B3:C8, COUNTIFS(E3:E4, B3:B8))
Figure 4 Filtering more than one customer
JORDAN GOLDMEIER
Top N Results
To see only top performers, FILTER can be
set to display the top N results, perfect
for identifying high achievers in your
data
=FILTER(B3:C9, C3:C9 >= LARGE(C3:C9, E3))
Figure 5 Filtering top N salesperson's data
JORDAN GOLDMEIER
Time-Sensitive Data
For inventory management, FILTER can
easily identify items nearing expiration,
providing timely insights for efficient
stock tracking
=FILTER(B3:D8, C3:C8 < TODAY() + 30)
Figure 6 Filtering expiring inventory
JORDAN GOLDMEIER
T h e E x c e l U N IQ U E f unc t io n r e t u r n s a l i s t
o f uniq ue va lue s in a lis t o r r a n g e .
V a lue s c an b e t e x t , num b e r s , d a t e s ,
times, etc.
E x t r a c t uniq ue va lue s f r o m r a n g e .
=UNIQUE(array, [by_col], [exactly_once])
T h i s f unc t io n is no t c a s e - s e n s i t i v e . I t
w i l l t r e a t " A PPL E " , " A p p le " , a n d " a p p l e "
a s t he s a m e t e x t .
JORDAN GOLDMEIER
Y o u c a n us e t he " U niq ue " f u n c t i o n t o
e x t r a c t d is t inc t p r o d uc t na m e s .
Figure 1 Extract Unique Products
JORDAN GOLDMEIER
Y o u c a n e x t r a c t d is t inc t C u s t o m e r s
w h ile a ut o m a t ic a lly ig no r in g a n y
b l a nk s .
Figure 2 Extract Unique Customers ignoring blanks
JORDAN GOLDMEIER
Y o u c a n c o unt U niq ue C us t o m e r s f o r
p e r f o r m a nc e m e t r ic s a nd a c c u r a t e
c u s t o m e r ins ig ht s .
Figure 3 Count Unique Customers
JORDAN GOLDMEIER
Y o u c a n a ls o f ilt e r a nd e x t r a c t u n i q u e
v a lue s f r o m a d a t a s e t b a s e d o n a
c r i t e r ia .
Figure 4 Unique Values based on Criteria
JORDAN GOLDMEIER
Y o u c a n r e m o ve d up lic a t e r o w s a c r o s s
a n e nt ir e d a t a s e t , e ns ur ing c l e a n a n d
a c c ur a t e d a t a f o r a na ly s is .
Figure 5 Remove Duplicates from a dataset
JORDAN GOLDMEIER
T o e x t r a c t c o m m o n va lue s b e t w e e n
t w o lis t s , U N IQ U E f unc t io n w o n ’ t h e l p .
F I L T E R a nd C O U N T IF S s a ve s t h e d a y .
Figure 6 Extract Common Values from Two Lists
JORDAN GOLDMEIER
Follow for more
Excel Content
Jordan Goldmeier
Send Follow Save
