# Lookup most frequent item [Homework]

Posted on February 17th, 2017 in Excel Challenges - 17 comments

Here is an interesting problem to keep your brain cells fight boredom on this Friday & weekend.

Let’s say you have some data like this.

And you want to know, for a given customer name (in cell G4),

• What is the most frequent quantity?
• What is the most often purchased item?

How would you write formulas to get these answers?

Assume that all the data is in a table, conveniently named data. So you can access customer names by using structured name – data[Customer] etc.

If there are ties, you may pick any one value.

Want more? Check out our Excel Homework problems page and get cracking.

 Use CTRL to make copies of worksheets quickly
 Written by Chandoo Tags: homework, Learn Excel, Microsoft Excel Formulas, mode(), Mode.mult(), vlookup Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 17 Responses to “Lookup most frequent item [Homework]”

1. mma173 says:

For most frequent quantity:
{=INDEX(data[Qty],SMALL(IF(COUNTIF(data[Qty],data[Qty])=MAX(COUNTIF(data[Qty],data[Qty])), ROW(data[Qty]),""),1))}

For most often purchased item:
{=INDEX(data[Item],SMALL(IF(COUNTIF(data[Item],data[Item])=MAX(COUNTIF(data[Item],data[Item])), ROW(data[Item]),""),1))}

• Edgars says:

Doesn't work, always returns the 4th value from the range data[Qty].

2. Hugo says:

first sort the table (high to low)
then with a simple index/match
(first occurrence of that name is the most frequent item for that name)

3. Desk Lamp says:

{=MODE(IF(data[Customer]=\$G4,data[Qty]))}

{=INDEX(data[Item],MATCH(TRUE,COUNTIFS(data[Item],IF(data[Customer]=\$G4,data[Item]),data[Customer],\$G4)=MAX(COUNTIFS(data[Item],IF(data[Customer]=\$G4,data[Item]),data[Customer],\$G4)),0))}

I think the second formula could be simplified but I'll leave it at that for now.

• Desk Lamp says:

Ok, slightly simpler:

{=INDEX(data[Item],MATCH(TRUE,COUNTIFS(data[Item],data[Item],data[Customer],\$G4)=MAX(COUNTIFS(data[Item],data[Item],data[Customer],\$G4)),0))}

• Daniel H says:

{=MODE(IF(G4=data[Customer],data[Qty]))}

Same as yours. It’s curious why Excel doesn’t treat “FALSE” as zero

{=INDEX(data[Item],MODE(IF(G4=data[Customer],MATCH(data[Item],data[Item],0))))}

It just works, don’t ask me why 😉

• David N says:

Daniel, that's a crazy cool and elegant solution!

My initial thought was to use FREQUENCY on the concatenation of Customer and Item because the COUNTIFS solutions would be very slow if the array were much larger. Then a MATCH on the MAX (or AGGREGATE) of that FREQUENCY would have determined the corresponding position. And that approach works, as shown below. It's long, but it's also always going to be fast.

You on the other hand recognized that the position of an Item relative to its Customer doesn't matter as long as every possible Item is represented by a unique position (number) and ignored unless it corresponds with the correct Customer. And your solution capitalizes on that perfectly. I also appreciate how your formula avoids repeating any of its pieces, something I particularly dislike and avoid whenever I can.

{=INDEX(data[Item],MATCH(AGGREGATE(14,6,IF(data[Customer]=G4,FREQUENCY(MATCH(data[Customer]&"#"&data[Item],data[Customer]&"#"&data[Item],0),MATCH(data[Customer]&"#"&data[Item],data[Customer]&"#"&data[Item],0))),1),IF(data[Customer]=G4,FREQUENCY(MATCH(data[Customer]&"#"&data[Item],data[Customer]&"#"&data[Item],0),MATCH(data[Customer]&"#"&data[Item],data[Customer]&"#"&data[Item],0))),0))}

4. Michael (Micky) Avidan says:

In cell H4: {=MAX((data3[Customer]=G4)*data3[Qty])}
In cell I4: {=INDEX(data3[Item],MATCH(G4&H4,data3[Customer]&data3[Qty],))}
and copy down...
***Both Array Formulas
--------------------------------
Michael (Micky) Avidan

• Michael (Micky) Avidan says:

It became DATA3 because I copied the sheet in oredr to work on a copy.
----------------------------
Michael (Micky) Avidan

• Michael (Micky) Avidan says:

Sorry..., I misssed the MODE part.
Ignore my posts.
----------------------------
Michael (Micky) Avidan

5. Ola says:

Make a PivotTable (sort) --> enables drilldown

6. Sabeesh says:

Qty: =MODE(IF(data[Customer]=G4,data[Qty]))

Item:=INDEX(data[Item],MATCH(MAX(COUNTIFS(data[Customer],G4,data[Item],data[Item])),COUNTIFS(data[Customer],G4,data[Item],data[Item]),0))

Both are array formulas

7. Sabeesh says:

=INDEX(data[Item],MATCH(MAX(COUNTIFS(data[Customer],G4,data[Item ],data[Item])),COUNTIFS(data[Customer],G4,data[Item],data[Item]),0))

• ZORRO2005 says:

Very good solution!

8. ak says:

Hello,

Sorry for posting here but I couldn't find a place to post questions here. Is there an area where I can post questions.

How can I delete every other row quickly in excel, because I ran a report and it skips a row for each entry.

Thanks so much and apologize if this is not allowed.

9. ZORRO2005 says:

{=INDEX(data[Item],MODE(IF(data[Customer]=G4,MATCH(data[Item],data[Item],))))}

10. Rojo Loco says:

Create a pivot table off the data. Put customer and items on rows, put count of item on values. Sort highest to lowest on count of item. Go to design view of pivot table and change report lay out to tabular and repeat values, turn off grand totals and subtotals. Each customer by each item now has a value sorted highest to lowest. Index match the customer name to the item and count.

This allows for changes to the pivot table to flow to the output, such as minimum value, average value (if your data allowed for it).

 Use CTRL to make copies of worksheets quickly