Excel Find Last Item In List Using MATCH But Not XMATCH - 2442

Share
MrExcel.com
Published at : 13 Nov 2021
995 views
77
0

There are a pair of well known Excel tricks with MATCH:
Find the last item in a long list of items that might contain empty cells.
Find the only number in a range of Error or Text values.
Excel tricksters have made use of these.
Why do they work?
And why do they not work with the new, superior XMATCH?

Thanks to Jose for sending this question in.

In this video, we dive in to how the binary search works in MATCH.

We see how to adjust XMATCH to find the last item in a list.

But there is no way to have XMATCH find the only number in a column of error cells. Which brings up the bigger question…. Why does this work at all with MATCH? It violates the rules of binary search.

Table of Contents
(0:00) Why can’t XMATCH do what MATCH can do?
(0:30) Finding last item in list with MATCH
(1:30) How a binary search works
(5:30) Why doesn’t the same trick work with XMATCH?
(7:55) Use MATCH to find only number in a range
(9:10) Excel Sort Sequence
(10:15) Why can’t XMATCH find the only number?
(12:10) Why is MATCH finding the last item? Is it a bug?

Microsoft ExcelExcelBill Jelen

The Most SECURE Bunker in Minecraft!

The Most SECURE Bunker in Minecraft!

लग्नासाठी लागणारे सामान कितीला विकत आणले | Cost of Jewellery We Brought | Shubhangi Keer

लग्नासाठी लागणारे सामान कितीला विकत आणले | Cost of Jewellery We Brought | Shubhangi Keer

🔴  Thunderstorm Sounds for Sleeping - 24/7 | Deep Sleep Sounds, Fall Asleep Fast, Beat Insomnia

🔴 Thunderstorm Sounds for Sleeping - 24/7 | Deep Sleep Sounds, Fall Asleep Fast, Beat Insomnia

As I May - High Octane (Official Music Video)

As I May - High Octane (Official Music Video)

Make a Useful Bending Tool at Home

Make a Useful Bending Tool at Home

Jimmy V - Cutting Down the Nets

Jimmy V - Cutting Down the Nets

The Girl Born With No Eyes | BORN DIFFERENT

The Girl Born With No Eyes | BORN DIFFERENT

Most Beatable Saw Traps

Most Beatable Saw Traps

MOMENTUM MONDAYS!! - Stock Market Live

MOMENTUM MONDAYS!! - Stock Market Live

Чоршанбе VS Калмыков. Конфликт в студии. Лендруш VS Самат. Финал гран-при. Назир VS Черняев

Чоршанбе VS Калмыков. Конфликт в студии. Лендруш VS Самат. Финал гран-при. Назир VS Черняев

Desire to End Desire

Desire to End Desire

This video is only for Students!

This video is only for Students!

Lucid (LCID) Q3 Earnings | LUCID Stock Crash Coming? | Rather Own TSLA?

Lucid (LCID) Q3 Earnings | LUCID Stock Crash Coming? | Rather Own TSLA?

Superfruit - GUY.exe (Lyrics) "6 feet tall and super strong" [Tiktok Song]

Superfruit - GUY.exe (Lyrics) "6 feet tall and super strong" [Tiktok Song]

It's Official - They Want Us Rounded Up

It's Official - They Want Us Rounded Up

How to Remove Time from Date Timestamp In Excel || Excel Tips & Tricks || dptutorials

How to Remove Time from Date Timestamp In Excel || Excel Tips & Tricks || dptutorials

Abraham Hicks - Stop Doubting And Start Believing | That's It!

Abraham Hicks - Stop Doubting And Start Believing | That's It!

Gen. Skrzypczak: Rosja nie ma dostatecznej przewagi, żeby pokonać Ukrainę

Gen. Skrzypczak: Rosja nie ma dostatecznej przewagi, żeby pokonać Ukrainę

The Very Best of JAZZ - Louis Armstrong, Frank Sinatra, Norah John, Diana Krall, Ella Fitzgerald

The Very Best of JAZZ - Louis Armstrong, Frank Sinatra, Norah John, Diana Krall, Ella Fitzgerald

IF OBJECTS WERE PEOPLE || Funny & Relatable Situations by KABOOM!

IF OBJECTS WERE PEOPLE || Funny & Relatable Situations by KABOOM!

Adventure to Zorro waterfall Trinidad... A guide to an outdoor Caribbean paradise !

Adventure to Zorro waterfall Trinidad... A guide to an outdoor Caribbean paradise !

Jack Ma's Companies Just Lost $780 Billion In 11 Months

Jack Ma's Companies Just Lost $780 Billion In 11 Months

FNF Reacts To "STRESS" but Every Turn Another Character Sing It - Friday Night Funkin Animation

FNF Reacts To "STRESS" but Every Turn Another Character Sing It - Friday Night Funkin Animation

Amply Meaning

Amply Meaning

Dr. Andrew Huberman — A Neurobiologist on Optimizing Sleep, Enhancing Performance, and More

Dr. Andrew Huberman — A Neurobiologist on Optimizing Sleep, Enhancing Performance, and More

Biggest HAUL ever + SALES! Prada, Bottega, Balenciaga.. | Tamara Kalinic

Biggest HAUL ever + SALES! Prada, Bottega, Balenciaga.. | Tamara Kalinic

Undies on a mission!! EBY undergarments review!

Undies on a mission!! EBY undergarments review!

BLAME THE GPS | My Trucking Life | Vlog #2398

BLAME THE GPS | My Trucking Life | Vlog #2398

A Vital Touch

A Vital Touch

Options trading with technical analysis - simple and effective 🔥🔥🔥

Options trading with technical analysis - simple and effective 🔥🔥🔥

We Pick Our Top 5 Heart Albums with Pete Pardo, Martin Popoff, and Sean Kelly

We Pick Our Top 5 Heart Albums with Pete Pardo, Martin Popoff, and Sean Kelly

the tactic video

the tactic video

Harmonize - Wishes (Official Audio)

Harmonize - Wishes (Official Audio)

🔴LIVE - DR DISRESPECT - VANGUARD WITH COURAGEJD | LEVELING UP WITH !DEWDUAL2XP #GameFuelPartner

🔴LIVE - DR DISRESPECT - VANGUARD WITH COURAGEJD | LEVELING UP WITH !DEWDUAL2XP #GameFuelPartner

1 ДЕН С КИРИЛ ПЕТКОВ И ПРОДЪЛЖАВАМЕ ПРОМЯНАТА

1 ДЕН С КИРИЛ ПЕТКОВ И ПРОДЪЛЖАВАМЕ ПРОМЯНАТА

Football Manager 2022: TOP 5 Teams To Manage | FM22 Gameplay

Football Manager 2022: TOP 5 Teams To Manage | FM22 Gameplay

OSRS - Tempoross 5 phase method (10510 points - 13 permits)

OSRS - Tempoross 5 phase method (10510 points - 13 permits)

LANKYBOX FUNNY ANIMATED MEMES! (FOXY SLAPS HUGGY WUGGY, JUSTIN & ADAM IN POPPY PLAYTIME, & MORE!)

LANKYBOX FUNNY ANIMATED MEMES! (FOXY SLAPS HUGGY WUGGY, JUSTIN & ADAM IN POPPY PLAYTIME, & MORE!)

Colin Flaherty: Racial Slurs and Fist Flying Swimming Pool Argy Bargy

Colin Flaherty: Racial Slurs and Fist Flying Swimming Pool Argy Bargy

Billiard Tutorial: How to change your pool cloth!!!

Billiard Tutorial: How to change your pool cloth!!!

Seebeck & Peltier Effect - How Thermocouples & Peltier Cells work?

Seebeck & Peltier Effect - How Thermocouples & Peltier Cells work?

Michael Bublé - White Christmas (ft. Shania Twain) [Official HD]

Michael Bublé - White Christmas (ft. Shania Twain) [Official HD]

Concerns as large crowds gather in Auckland breaking Covid restrictions

Concerns as large crowds gather in Auckland breaking Covid restrictions

More than 54.8 million people affected by flooding in China

More than 54.8 million people affected by flooding in China

Crypto Advanced Show 78 Επιστρέφουμε δυναμικά!

Crypto Advanced Show 78 Επιστρέφουμε δυναμικά!

HANG OUT WITH US FOR A COUPLE DAYS IN MIAMI | thanksgiving prep + thanksgiving 2021 | KAYLA BUELL

HANG OUT WITH US FOR A COUPLE DAYS IN MIAMI | thanksgiving prep + thanksgiving 2021 | KAYLA BUELL

How to Beat a Tesla Plaid for Cheap

How to Beat a Tesla Plaid for Cheap

FORTYFOUR - SWEET DREAMS

FORTYFOUR - SWEET DREAMS

MY COLLECTION IS LIVE!

MY COLLECTION IS LIVE!