Pulling a column header based on a cells lowest value

kcgojnur

Board Regular
Joined
Aug 13, 2014
Messages
122
Hi -

I'm wondering if someone can help me out. I have a table that has carrier name and below the name is the various prices. Some carriers will have "N/A" if they do not service certain regions. I'm trying to find a way that will review all the rates and provide to me the carrier name that offers the cheapest rate. I've tried to ways to accomplish this but I'm getting an error.

1st way was
Code:
=INDEX(F55:M55,MATCH(C87,$F$56:$O$59,0))
In which cell c87 was created to pull the minimum rate.

2nd way was
Code:
=INDEX(F55:M55,MATCH(MIN(F56:O59),F56:O59,0))

Both ways are giving me N/A. Is it because some of the values in my table have "N/A"?

Any help is greatly appreciated.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Aetna
[/TD]
[TD]BCBS[/TD]
[TD]Kaiser[/TD]
[TD]United
[/TD]
[TD]Carrier E
[/TD]
[TD]Carrier F
[/TD]
[/TR]
[TR]
[TD]352.50
[/TD]
[TD]5000
[/TD]
[TD]N/A
[/TD]
[TD]785.26
[/TD]
[TD]626.50
[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]756[/TD]
[TD]856[/TD]
[TD]999
[/TD]
[TD]2000
[/TD]
[TD]N/A[/TD]
[TD]N/A
[/TD]
[/TR]
</tbody>[/TABLE]






To provide additional context to my initial inquiry. Also, is there a way to only review and pull the lowest rate on specific carriers for example pull the lowest rate between Aetna, BCBS and United?
 
Upvote 0
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

This formula would take into account if more than one carrier has the same lower rate. If only one carrier has the lowest rate and your cell C87 shows this rate then the formula could shortened to (this is also an array formula):
Code:
=INDEX($F$55:$M$55,SMALL(IF(IF(ISNUMBER($F$56:$M$59),$F$56:$M$59)=C87,COLUMN($F$55:$M$55)-COLUMN($F$55)+1),1))

Also, not sure why your INDEX shows column headers as columns F55:M55 and then your array of rates as going to column O?
Excel Workbook
DEFGHIJKLM
55Lowest RateUS ExpressHuntUSACarrier3Carrier4Carrier5Carrier6Carrier7
56Carrier(s)#N/A10455#N/A185
57Hunt151#N/A4#N/A2917
58Carrier51681931817#N/A
59174#N/A1616191610
60
Sheet
 
Upvote 0
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Aetna[/TD]
[TD]BCBS[/TD]
[TD]Kaiser[/TD]
[TD]United[/TD]
[TD]Carrier E[/TD]
[TD]Carrier F[/TD]
[/TR]
[TR]
[TD]352.50[/TD]
[TD]5000[/TD]
[TD]N/A[/TD]
[TD]785.26[/TD]
[TD]626.50[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]756[/TD]
[TD]856[/TD]
[TD]999[/TD]
[TD]2000[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]






To provide additional context to my initial inquiry. Also, is there a way to only review and pull the lowest rate on specific carriers for example pull the lowest rate between Aetna, BCBS and United?


match only works for a row or a column. without using array, why don' you just have a row at the top that calculates minimum by carrier, and have the index match that looks for minimum by carrier?
 
Upvote 0
Thanks I'll give this a try. The index column should have extend to column O. If I wanted to have the same functionallity, but only pull the lowest rate from specific carriers how would that be modified?
 
Upvote 0
This is only a snapshot. I'm not sure I follow your suggestion. I have these rates based on carrier. I need to pull only the lowest rate in order to determine subsidy. Do you mind showing what you are suggesting? Also, I added the array but it still wasn't working.
 
Upvote 0
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

This formula would take into account if more than one carrier has the same lower rate. If only one carrier has the lowest rate and your cell C87 shows this rate then the formula could shortened to (this is also an array formula):
Code:
=INDEX($F$55:$M$55,SMALL(IF(IF(ISNUMBER($F$56:$M$59),$F$56:$M$59)=C87,COLUMN($F$55:$M$55)-COLUMN($F$55)+1),1))

Also, not sure why your INDEX shows column headers as columns F55:M55 and then your array of rates as going to column O?

DEFGHIJKLM
#N/A#N/A
#N/A#N/A
#N/A
#N/A

<colgroup><col style="width:30px; "><col style="width:143px;"><col style="width:28px;"><col style="width:77px;"><col style="width:64px;"><col style="width:64px;"><col style="width:84px;"><col style="width:84px;"><col style="width:84px;"><col style="width:84px;"><col style="width:84px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Lowest Rate[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]US Express[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Hunt[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]USA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Carrier3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Carrier4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Carrier5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Carrier6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Carrier7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]56[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Carrier(s)[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]57[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Hunt[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]58[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Carrier5[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]59[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

</tbody>

Spreadsheet Formulas
CellFormula
D57{=IFERROR(INDEX($F$55:$M$55,SMALL(IF(IF(ISNUMBER($F$56:$M$59),$F$56:$M$59)=AGGREGATE(5,6,$F$56:$M$59),COLUMN($F$55:$M$55)-COLUMN($F$55)+1),ROWS($D$57:D57))),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


When I try the formula you provided in the worksheet I'm using I get #Value , however when I duplicate on a new worksheet it seems to work. I verified all referenced cells are either general or $ value. I'm not quite sure why I'm getting the error.
 
Upvote 0
You will get the #VALUE error if you don't enter the formula as an array. If you get the error try this:
-Put the cursor in the cell with the formula.
-Press the F2 key to edit
-Then press CTRL-SHIFT-ENTER.

Right now I'm not sure on how to just pick certain carriers with a formula, maybe someone else will have an idea (may need a VBA solution).
 
Upvote 0
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Aetna[/TD]
[TD]BCBS[/TD]
[TD]Kaiser[/TD]
[TD]United[/TD]
[TD]Carrier E[/TD]
[TD]Carrier F[/TD]
[/TR]
[TR]
[TD]352.50[/TD]
[TD]5000[/TD]
[TD]N/A[/TD]
[TD]785.26[/TD]
[TD]626.50[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]756[/TD]
[TD]856[/TD]
[TD]999[/TD]
[TD]2000[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]






To provide additional context to my initial inquiry. Also, is there a way to only review and pull the lowest rate on specific carriers for example pull the lowest rate between Aetna, BCBS and United?

[TABLE="class: grid, width: 606"]
<colgroup><col span="4"><col><col><col><col></colgroup><tbody>[TR]
[TD]Aetna[/TD]
[TD]BCBS[/TD]
[TD]Kaiser[/TD]
[TD]United[/TD]
[TD]Carrier E[/TD]
[TD]Carrier F[/TD]
[TD] [/TD]
[TD]Aetna[/TD]
[/TR]
[TR]
[TD]352.5[/TD]
[TD]5000[/TD]
[TD]N/A[/TD]
[TD]785.26[/TD]
[TD]626.5[/TD]
[TD]N/A[/TD]
[TD] [/TD]
[TD]BCBS[/TD]
[/TR]
[TR]
[TD]756[/TD]
[TD]856[/TD]
[TD]999[/TD]
[TD]352.5[/TD]
[TD]N/A[/TD]
[TD]300[/TD]
[TD] [/TD]
[TD]United[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]lowest rate[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]352.5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]carrier(s)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Aetna[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]United[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

In H5 control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(MATCH(A1:F1,H1:H3,0)),A2:F3))

In H7 control+shift+enter, not just enter, and copy down

=IFERROR(INDEX($A$1:$F$1,SMALL(IF(ISNUMBER(MATCH($A$1:$F$1,$H$1:$H$3,0)),IF($A$2:$F$3=H$5,COLUMN($A$1:$F$1)-COLUMN($A$1)+1)),ROWS($H$7:H7))),"")
 
Upvote 0
You will get the #VALUE error if you don't enter the formula as an array. If you get the error try this:
-Put the cursor in the cell with the formula.
-Press the F2 key to edit
-Then press CTRL-SHIFT-ENTER.

Right now I'm not sure on how to just pick certain carriers with a formula, maybe someone else will have an idea (may need a VBA solution).


Thanks! That seemed to work.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top