Finding lowest cost carrier amoung an arry.

[kcgojnur]

Hello -

I have a spread sheet that has multiple carriers, metallics and rates. I'm looking to find the lowest carrier among bronze rates.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Aetna
[/TD]
[TD]BCBS
[/TD]
[TD]Health Net
[/TD]
[TD]Dean
[/TD]
[TD]Geisinger
[/TD]
[/TR]
[TR]
[TD]Bronze
[/TD]
[TD]10
[/TD]
[TD]N/A
[/TD]
[TD]100
[/TD]
[TD]35
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Bronze Plus
[/TD]
[TD]15
[/TD]
[TD]N/A
[/TD]
[TD]50
[/TD]
[TD]45
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Gold
[/TD]
[TD]20
[/TD]
[TD]N/A
[/TD]
[TD]75
[/TD]
[TD]55
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]Silver
[/TD]
[TD]30
[/TD]
[TD]N/A
[/TD]
[TD]25
[/TD]
[TD]65
[/TD]
[TD]20
[/TD]
[/TR]
</tbody>[/TABLE]

Looking to find a way that I can pull the name of the lowest cost carrier (Geisinger) in my example above. Table range is E55:O60.

I'm looking to have this the result in C87

Thanks.

 

[Aladin Akyurek]

In C87 contro+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$5,MATCH("bronze",$A$2:$A$5,0),0)=MIN(INDEX($B$2:$F$5,MATCH("bronze",$A$2:$A$5,0),0)),COLUMN($B$2:$F$5)-COLUMN($B$2)+1),ROWS($C$87:C87))),"")

This formula won't miss multiple carries if they are equally cheap.

 

[kcgojnur]

In C87 contro+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$5,MATCH("bronze",$A$2:$A$5,0),0)=MIN(INDEX($B$2:$F$5,MATCH("bronze",$A$2:$A$5,0),0)),COLUMN($B$2:$F$5)-COLUMN($B$2)+1),ROWS($C$87:C87))),"")

This formula won't miss multiple carries if they are equally cheap.

Worked like a charm! Thanks for your help.

 

[Aladin Akyurek]

Worked like a charm! Thanks for your help.

You are welcome.

 

[kcgojnur]

You are welcome.

Hi Aladin

Is there a way to use the formula w/out using { }? Our development team is running into issues and wondered if there was a work around?

 

[Aladin Akyurek]

What are the issues the team is running into?

 

[kcgojnur]

What are the issues the team is running into?

I can't comment specifically on what the issue is however, they are saying b/c of { } it's causing an issue. Any thoughts?

 

[Aladin Akyurek]

I can't comment specifically on what the issue is however, they are saying b/c of { } it's causing an issue. Any thoughts?

It's mandatory in Excel to signal array-processing formulas with SUM, COUNT, AVERAGE, and so on with control+shift+enter, also when the formula is edited.

 

[kcgojnur]

It's mandatory in Excel to signal array-processing formulas with SUM, COUNT, AVERAGE, and so on with control+shift+enter, also when the formula is edited.

Thanks Aladin - at least I know for the future.

 

[Aladin Akyurek]

Thanks Aladin - at least I know for the future.

Glad to help.