ILTER Replace text with REPLACE() and SUBSTITUTE()

Data Manipulation in Excel

Subset Arrays for Multiple Rows with F

Filter
an array for values that match a value with F ILTER() — Same as =XLOOKUP("Nigeria", A2:A11, B2:D11)
=REPLACE(B2:B11, 2, 1, "X") Replace a substring by position with REPLACE()

=FILTER(B2:D11, A2:A11="Nigeria")

=SUBSTITUTE(B2:B11, "N", "X") Replace specific characters with SUBSTITUTE()
Learn Excel online at www.DataCamp.com Where the lookup value does not match a key, provide a default value with FILTER(if_empty)
Kingdom", A2:A11, B2:D11, "Country not found")

— Same as =XLOOKUP("United

=FILTER(B2:D11, A2:A11="United Kingdom", "Country not found")

FILTER can also return multiple rows

=FILTER(A2:D11, D2:D11<10)

> Work with Cell Positions & References

Combine criteria using logical AND with FILTER(include1 * include2) — For text data < means "preceding alphabetically"
=CHOOSE(RANDBETWEEN(1, 4), A2:A11, B2:B11, C2:C11, D2:D11) Choose a return value from the input with CHOOSE()

=FILTER(A2:D11, (A2:A11 < "N") * (D2:D11 > 100))

> Dataset

Combine criteria using logical OR with FILTER(include1 + include2)

=INDIRECT(F1) Get the value in a reference to a cell with INDIRECT() — Suppose cell F1 contains the text value "A1"

=FILTER(A2:D11, (C2:C11 = ".in") + (C2:C11 = ".id"))

The main dataset contains details for the ten most populous countries. =OFFSET(A2, 0, 3) Get the value in a cell by position relative to another cell with OFFSET()

- A B C D
Fin d Positions in Lists with XMATCH() =ROWS(A2:A11) Get the number of rows in an array with ROWS()

1 Country Country code Internet TLD Phone prefix code Get the position in a list of the first exact match of a value with XMATCH()
=COLUMNS(A2:D2) Get the number of columns in an array with COLUMNS()

2 China CHN .cn 599 =XMATCH("Brazil", A2:A11)

3 India IND .in 91 =ROW(A2:A11) Get the number of row number of cells with ROW()

Get the position in a list of the first match that starts with a value with XMATCH(match_mode=1)

4 United States USA .us 1 =XMATCH("I", A2:A11, 1)

=COLUMN(A2:D2) Get the number of column number of cells with COLUMN()

5 Indonesia IDN .idn 62 Get the position in a list of the first match using wildcards with XMATCH(match_mode=2)

6 Pakistan PAK .pk 92 =XMATCH("Me?ico", A2:A11, 2)

Brazil BRA .br 55 Fordata sorted in ascending order, use faster binary search for same task XMATCH(search_mode=2)

Nigeria NGA .ng 234

=XMATCH("China", SORT(A2:A11), , 2)
> Calculate with Database-like Filters
Bangladesh BGD .bd 880
Russia RUS .ru 7
Get Values by Position with INDEX Assume an additional dataset in the worksheet containing filter conditions. Perform calculations using database-like filter conditions
with D*()

Mexico MEX .mx 52 Get the value by row and column number within an array with INDEX() — Row and column numbers start from 1rom 1

=INDEX(A2:D11, 5, 3)

Find the maximum of elements matching filters

=DMAX(A1:D11, "Phone prefix code", A10:D15)

Get the value that matches a condition with XMATCH() and INDEX() combined

=INDEX(A2:D11, XMATCH("Brazil", A2:A11), XMATCH("Country code", A1:D1))

COUNT of elements matching filters

> Wildcards Sort Arrays with SORT and SORTBY

=DCOUNT(A1:D11, "Phone prefix code", A10:D15)

SUM of elements matching filters

Many data manipulation functions let you match any text character using wildcards. Sort an array in ascending order of values in a column with SORT()
=DSUM(A1:D11, "Phone prefix code", A10:D15)

=SORT(A2:D11, 3)

- A B AVERAGE of elements matching filters

Sort an array in descending order of values in a column with SORT(sort_order=-1)

? "gr?y" "grey" "gray" =DAVERAGE(A1:E11, "GDP", A10:E15)

Match 1 character matches and =SORT(A2:D11, 3, -1) 


* Match 0 or more characters "sp*y" matches "spy", "spry", and "springy" STDEV of elements matching filters

Sort an array by values of another array with SORTBY()

~ Escape wildcard character "~?~*~~" matches "?*~" =SORTBY(A2:D11, C2:C11)

=DSTDEV(A1:E11, "GDP", A10:E15)

<> Match not blank "<>" matches "anything"

Sort an array by multiple arrays (for example breaking ties with values from second column) 

=SORTBY(A2:D11, A2:A11, 1, B2:B11, -1)

Database calculation functions and conditional calculation functions allow numeric criteria wildcards.
Randomize row order with SORTBY() + RANDARRAY()

- A B =SORTBY(A2:D11, RANDARRAY(COUNTA(A2:A11)))

> Match values greater than `>10` matches values greater than 10

<= Matches values less than or equal to <=10 matches values less than or equal to 10

= Match values equal to =10 matches values equal to 10

<> Match values not equal tor <>10 matches values not equal to 10 > Work with Text Data
Clean text with TRIM() and CL EAN()
Trim all white space except single spaces between words with TRIM()

> Data Transformation =TRIM(" Only single spaces between words remain ")

Remove non-printable characters with CLEAN() — CHAR(7) is an alarm bell sound

Subset Arrays for a Single Row with XLOOKUP =CLEAN("alarm" & CHAR(7))

Get the rows of a return array where the keys match a value with XLOOKUP()

Fin d Substrings with FIND()

=XLOOKUP("Nigeria", A2:A11, B2:D11)

the position of the first instance of a character sequence with IND()

Learn Excel Online at

www.DataCamp.com
Where the lookup value does not match a key, provide a default value with XLOOKUP(if_not_found)
Find F

=XLOOKUP("United Kingdom", A2:A11, B2:D11, "Country not found")

=FIND("ia", A2:A11)

Where the lookup value does not match a key, return the next largest value with XLOOKUP(match_mode=1)

=XLOOKUP("United Kingdom", A2:A11, B2:D11, #N/A, 1)

Join & Split Text with TEXTJOIN() and TEXTSPLIT()
Collapse an array of text to a single cell with TEXTJOIN()

Left joins with XLOOKUP() =TEXTJOIN(";", TRUE, A2:A11)

- F G H I J Split a cell by a delimiter with TEXTSPLIT()

=TEXTSPLIT(A4, " ")

1 Landmark Address City State Country

2 Taj Mahal Dharmapuri Agra Uttar Pradesh India

Split text on multiple delimiters with TEXTSPLIT(delimiter={array})

=TEXTSPLIT(A4, {"a","e"})
3 Empire State 350 5th Avenue New York New York United States

4 Winter Palace 32 Palace 
 St Petersburg Northwestern 
 Russia

Embankment District

5 Al Hambra C. Real de la 
 Granada Andalusia Spain

Alhambra

Left join two datasets with XLOOKUP() — Copy formula down the J column to complete the join

=XLOOKUP(J2, $A$2:$A$11, $B$2:D$11)