Select currency name based on count in another range

[Wainers]

Hi
I have a range of currency short-names in row 1 and a count of currencies in row 3 (see screen shot). I need a single cell formula that will return the currency name if the count is greater than zero (only one count value will be non-zero).



Any help would be very much appreciated.
Wainers

 

[NARAYANK991]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=INDEX($A$1:$C$1,MAX(($A$3:$C$3<>0)*(COLUMN($A$1:$C$1))))

Narayan

 

[bines53]

Hi ,

It is also possible that,

=INDEX(A1:C1,,AGGREGATE(14,6,COLUMN(A1:C1)/((A3:C3)=MAX(A3:C3)),1))

David

 

[John Jairo V]

Hi to all!

Another one:
=LOOKUP(1,1/FREQUENCY(0,1/(1+A3:C3)),A$1:C$1)

Blessings!

 

[Wainers]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=INDEX($A$1:$C$1,MAX(($A$3:$C$3<>0)*(COLUMN($A$1:$C$1))))

Narayan

Great solution, Narayan. Worked a treat. I did need to adjust the "*COLUMN()" part so that it would calculate when the data does not start in column A. Thank you.

 

[Wainers]

Hi ,

It is also possible that,

=INDEX(A1:C1,,AGGREGATE(14,6,COLUMN(A1:C1)/((A3:C3)=MAX(A3:C3)),1))

David

Excellent, David. I'd never thought of using the AGGREGATE function, definitely one for me to investigate further, and it doesn't require CSE! Thank you.

 

[Wainers]

Hi to all!

Another one:
=LOOKUP(1,1/FREQUENCY(0,1/(1+A3:C3)),A$1:C$1)

Blessings!

Wow! Straight out of 'left field'. The shortest and neatest solution. Now I just need to figure out why it works. Thank you so much.