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

Share this & earn $10
MrExcel.com
Published at : 15 Nov 2021
1325 views
91
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

Beginning a Road Trip | Jalopy

Beginning a Road Trip | Jalopy

Ελευθερία Ελευθερίου - Calm Down feat. Koki | Official Video Clip

Ελευθερία Ελευθερίου - Calm Down feat. Koki | Official Video Clip

Can I Be MVP Of This Pro-AM League? NBA 2K22

Can I Be MVP Of This Pro-AM League? NBA 2K22

Nastya loves to discover something new for herself / kids stories

Nastya loves to discover something new for herself / kids stories

Living with a Kangaroo Named Dababy (Update)

Living with a Kangaroo Named Dababy (Update)

Jordan Peterson - What To Do If I Don't Have Any Goals?

Jordan Peterson - What To Do If I Don't Have Any Goals?

Why Should I Incorporate - The Company Corporation

Why Should I Incorporate - The Company Corporation

2.6 Hog vs Hard Match ups 🔥

2.6 Hog vs Hard Match ups 🔥

Skream - Anticipate (Ft. Sam Frank) (Netsky Remix)

Skream - Anticipate (Ft. Sam Frank) (Netsky Remix)

TWENTYFIRST NIGHT - SELAMANYA IINDONESIA  (Official Music Video)

TWENTYFIRST NIGHT - SELAMANYA IINDONESIA (Official Music Video)

THE BEST PARENTING HACKS FOR SMART MOMS & DADS

THE BEST PARENTING HACKS FOR SMART MOMS & DADS

A Method To x100 Your Productivity | Robin Sharma

A Method To x100 Your Productivity | Robin Sharma

WAP (feat. Megan Thee Stallion)

WAP (feat. Megan Thee Stallion)

White Lion - You're All I Need Lyrics

White Lion - You're All I Need Lyrics

Breath of the Wild | Good-Sized Horse | Walkthrough Guide

Breath of the Wild | Good-Sized Horse | Walkthrough Guide

50 Facts Only the Most Knowledgeable People Know

50 Facts Only the Most Knowledgeable People Know

ANTONIO CONTE"S FIRST GAME AS SPURS MANAGER VLOG 🇮🇹 5 GOALS & 3 RED CARDS!! 👀🔥

ANTONIO CONTE"S FIRST GAME AS SPURS MANAGER VLOG 🇮🇹 5 GOALS & 3 RED CARDS!! 👀🔥

Lots of life updates!

Lots of life updates!

Amorf - Anlasana

Amorf - Anlasana

How To Make a Car - Powered Car - Very Simple

How To Make a Car - Powered Car - Very Simple

BLACKPINK - 'How You Like That' DANCE PERFORMANCE VIDEO

BLACKPINK - 'How You Like That' DANCE PERFORMANCE VIDEO

SMART LIFE HACKS FOR EASY LIFE || 7 Brilliant Tips For Your Life by 123GO! Play

SMART LIFE HACKS FOR EASY LIFE || 7 Brilliant Tips For Your Life by 123GO! Play

Surgeon | Boiler Room x Eristoff 'Into The Dark' Porto

Surgeon | Boiler Room x Eristoff 'Into The Dark' Porto

Top 10 Most Dramatic Footage of Natural Disasters Caught on Camera

Top 10 Most Dramatic Footage of Natural Disasters Caught on Camera

UAE procuring ISRO's rocket

UAE procuring ISRO's rocket

The Obligation

The Obligation

Use of Seem in English | Seem to be | Seem to have Verb-3 | Seem to be Verb-ing | Learn English

Use of Seem in English | Seem to be | Seem to have Verb-3 | Seem to be Verb-ing | Learn English

BEST PARENTING HACKS FOR SMART MOMS AND DADS

BEST PARENTING HACKS FOR SMART MOMS AND DADS

Fastest Workers In The World

Fastest Workers In The World

Jordan Peterson - How To Turn On Your Inner Beast And Accomplish Anything

Jordan Peterson - How To Turn On Your Inner Beast And Accomplish Anything

SUNGLASSES (Club Mix)

SUNGLASSES (Club Mix)

Possibility by Lykke Li Lyrics

Possibility by Lykke Li Lyrics

Kids vocabulary - Health Problems - hospital play - Learn English for kids

Kids vocabulary - Health Problems - hospital play - Learn English for kids

MAMA YAKE MR. BENEFICIAL AGEUKIA COMEDY "MIMI NI PISI KALI, PESA KIDOGO DIET"

MAMA YAKE MR. BENEFICIAL AGEUKIA COMEDY "MIMI NI PISI KALI, PESA KIDOGO DIET"

RetcH - Actual Facts

RetcH - Actual Facts

2 Chainz - It's A Vibe ft. Ty Dolla $ign, Trey Songz, Jhené Aiko (Official Music Video)

2 Chainz - It's A Vibe ft. Ty Dolla $ign, Trey Songz, Jhené Aiko (Official Music Video)

ASMR Slappin' Inaudible Whispers

ASMR Slappin' Inaudible Whispers

УЯТСИЗ ВИДЕОНИ КИМ ТАРҚАТДИ? ФЕРУЗА БАБАШЕВА ДЕПУТАТЛИККА НОЛОЙИҚМИ? – EFFECT.UZ

УЯТСИЗ ВИДЕОНИ КИМ ТАРҚАТДИ? ФЕРУЗА БАБАШЕВА ДЕПУТАТЛИККА НОЛОЙИҚМИ? – EFFECT.UZ

Following a couple to check any funny business #vlogs

Following a couple to check any funny business #vlogs

CHANGED REVIEW

CHANGED REVIEW

Drake - Wants and Needs (Lyrics) ft. Lil Baby

Drake - Wants and Needs (Lyrics) ft. Lil Baby

The 100 Trailer

The 100 Trailer

SURPRISING PIPER WITH A CHILDISH ROOM MAKEOVER!!!! (She Got Mad) | Piper Rockelle

SURPRISING PIPER WITH A CHILDISH ROOM MAKEOVER!!!! (She Got Mad) | Piper Rockelle

Dj Roland Clark presents Urban Soul - Have A Good Time (Monkey Safari Remix)

Dj Roland Clark presents Urban Soul - Have A Good Time (Monkey Safari Remix)

Top 10 Animals Jukes Allowing Them to Barely Escape Their Predators

Top 10 Animals Jukes Allowing Them to Barely Escape Their Predators

Tower of Power - "This Type Of Funk" (Official Audio)

Tower of Power - "This Type Of Funk" (Official Audio)

System Of A Down - Toxicity (Official HD Video)

System Of A Down - Toxicity (Official HD Video)

D'banj - Stress Free [Feat. Seun Kuti & Egypt 80] (Official Music Video)

D'banj - Stress Free [Feat. Seun Kuti & Egypt 80] (Official Music Video)

Best songs to boost your mood ~ A playlist for study, travel, relax & fun

Best songs to boost your mood ~ A playlist for study, travel, relax & fun