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.

	Aetna	BCBS	Health Net	Dean	Geisinger
Bronze	10	N/A	100	35	5
Bronze Plus	15	N/A	50	45	10
Gold	20	N/A	75	55	15
Silver	30	N/A	25	65	20

<tbody>
</tbody>

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.