Pulling a column header based on a cells lowest value

K

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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
[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
kcgojnur said:
[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?
Click to expand...


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
AhoyNC said:
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
Click to expand...


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
kcgojnur said:
[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?
Click to expand...

[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
AhoyNC said:
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).
Click to expand...


Thanks! That seemed to work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
768
Nathan Asius
N
D
  • Question Question
Copy/Pasting rows of a table to specific cells of a separate worksheet template and exporting as a new file.
Replies
0
Views
218
DjentChicken
D
etaf
  • Solved
SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365
Replies
9
Views
810
Georgiboy
Georgiboy
SkywardPalm
  • Question Question
Populate blank cells based on column values from multiple sheets, using Item Number and column headers
Replies
1
Views
809
SkywardPalm
SkywardPalm
jski21
  • Question Question
Build Table Based on Cell Values
Replies
0
Views
334
jski21
jski21

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

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