Scribd
Upload
8 views

Amazing Excel Function Guide

Uploaded by

asce.luffy.soba
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views

Amazing Excel Function Guide

Uploaded by

asce.luffy.soba
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 25

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

You might also like