There are different solutions to find max n values in a row and return headers in Google Sheets. I recommend the Filter-Large combo.

Some of the suitable functions, better to say combinations, are Hlookup-Large, Index-Match-Large, or Filter-Large.

I am new to these Google Sheets functions. Any guide to learning them quickly?

Yes! I recommend my Google Sheets Functions Guide to those who are new.

All three combinations work equally well to find max n values in a row and return headers in Google Sheets. But the Filter-Large combo has one peculiarity.

No doubt, I will provide you with all three different formula combinations.

But first, understand how the first two combination formulas differ from the last.

In this, the max two values are 18,239.00 and 11,689.00. The headers of the respective values are “India” and “Saudi Arabia.”

By using the function Large, we can find max n values. For example, to find the max 1, you can use the formula below.

=large(C4:L4,1)

To find the max second value, modify the formula as below.

=large(C4:L4,2)

But we can’t use this formula to find the headers.

For that, we can use Hlookup and use either of the above two formulas as the search key.

One problem may surface. What’s that?

The Hlookup function can only search across the first row in a table.

Since we are using the Large formula as the search key, the output of this large formula is not from the first row in the table.

So we must tweak the range C3:L4 in Hlookup as below to move headers down the order.

={C4:L4;C3:L3}

Before heading to the formula, let me explain why the Filter combination is better.

When you have a max value that repeats, the Hlookup-Large combo would only return the same header for the first and second values. 

The same is the case with the Index-Match-Large combo.

How to Find Max N Values in a Row and Return Headers in Google Sheets

If you don’t have any duplicate values in the row, then use the first two formulas.

1. Hlookup-Large

=iferror(Hlookup(large(C4:L4,1),{C4:L4;C3:L3},2,false))

This HLOOKUP formula would return the name “India,” which is the header of the first max value.

Just change 1 (highlighted) to 2 in the LARGE formula to extract the header of the second max value.

The result would be “Saudi Arabia.” Change the number 2 to 3 to find the max third value in a row and return its headers.

2. Index-Match-Large

I’m also not recommending this INDEX formula to find max n values in a row and return headers if the numbers have duplicates.

=iferror(INDEX(C3:L3,MATCH(LARGE(C4:L4,1),C4:L4,0)))

Here also, modify the number (n) in the Large to return the header of the second, third, etc., large values.

3. Filter-Large (Recommended!)

So here is the final formula, which I recommend to my readers.

=textjoin(", ",true,iferror(filter(C3:L3,C4:L4=large(unique(C4:L4,true),1)),""))

This FILTER-based formula filters the header row C3:L3 for the UNIQUE max value in C4:L4.

So simple, and again the Large function plays an important role.

That means you can change the number 1 in the formula to 2 to return the header of the second largest value.

How is this formula different from the other two?

To explain that, I am just manipulating the above sample data.

Note:- This is just sample data. The values are not correct. I have manipulated the values for formula explanation purposes.

If you use the above filter formula, now with this data, it would return the names “India” and “Saudi Arabia” since both shares the same max value.

What about the other two combinations?

Both the formulas would return the country name “Saudi Arabia” for the large 1 and the large 2.

That means the Hlookup and Index omit the country “India” even though it’s the max 1 value header along with Saudi Arabia.

So choose the formulas as per your particular requirement.

Find Max N Values in Every Row and Return Headers – Lambda

All the above formulas lack multi-row capability.

So when you have values in more than one row below the header row, you have two options.

1. Make C3:L3 absolute in the above formulas, i.e., $C$3:$L$3, and copy-paste it (the formula) down.
2. Use BYROW Lambda Helper Function (LHF) to automatically spill the formulas down.

Here are those spill-down formulas to find max n values in every row and return headers in Google Sheets.

1. Hlookup-Large Spill Down Formula:

=byrow(C4:L,lambda(r, iferror(Hlookup(large(r,1),{r;C3:L3},2,false))))

2. Index-Match-Large Spill Down Formula:

=byrow(C4:L,lambda(r, iferror(INDEX(C3:L3,MATCH(LARGE(r,1),r,0)))))

3. Filter-Large Spill Down Formula (Recommended!):

=byrow(C4:L,lambda(r, textjoin(", ",true,iferror(filter(C3:L3,r=large(unique(r,true),1)),""))))

That’s all. Thanks for the stay. Enjoy!

Related:- Column Header of Max Value in Google Sheets Using Array Formula.