Finding most-often (frequency) works or gives REF error

Hi,

I'm stumped. Am I missing something, or is there a glitch in Excel?

Basically, these two formulas are the same. They just point to different lists, meaning different columns. One points to the LOCATION column. For the other points to the LEAD column. Other than that they are the same.

One works, one does not.

ENTER THE ACCOUNT NUMBER IN CELL E2.
The formulas point to E2 during calculation.

This works: I'll call it the LOCATION formula since it returns a city name
Cell F3:

Please Login or Register  to view this content.

This does not work. I'll call it the LEAD formula since it returns a salesperson's name
Cell F4:

Please Login or Register  to view this content.

Screenshot 2023-07-13 232206.png

Why?

A little background on the formula:

• The formula has 4 parts to it
• X counts the number of instances the account number appears in the list - that is, column A.
• Y executes an INDEX/MODE/MATCH thing to find the most frequent item, be it pointing to the LOCTION or LEAD column. INDEX/MODE/MATCH only works with more than 1 instance. Hence, the COUNTIFS to determine the number of instances of the account number are in column A.
• I found Y at Exceljet
• Z performs a simple XLOOKUP if only one instance is found since XLOOKUP works like that.
• The formula if X>1 then Y else Z allows for flow.

Spreadsheet attached.

Thanks.

Try this,

E2

Please Login or Register  to view this content.

F2

Please Login or Register  to view this content.

Windknife, you're saying I needed to restate the FILTER sections in order for this to work, like so...

Attachment 836442

Thank you. That makes sense. And I see why you added the additional variable in order to keep from repeating the FILTER section.

Glenn. That is a flex! Like a HAL9000 flex.

I dont even know what I'm looking at here.

And it works. That's just awesome.

I'll unpack your bit of spellwork when I can.

Thank you for the insight on what's available in Excel.

You are welcome.