Channel: MrExcel.com

Category: Howto & Style

Tags: mrexcelhow does excel sort numbers text boolean errors empty cellsfinding last value in a row with matchwhy doesn't xmatch find last value like match?is match better than xmatch?microsoft excelbill jelenexcelfinding the last value in a column with matchexcel hacksxmatch versus matchwhy doesn't xmatch find the only numberis this a bug in match?excel formulashow to find the only number using match

Description: 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?