Pulling a column header based on a cells lowest value

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

Please Login or Register  to view this content.

In which cell c87 was created to pull the minimum rate.

2nd way was

Please Login or Register  to view this content.

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.

Also, is there a way to pull the lowest rate from a specific group of carrier, for example pull the lowest rate out of Aetna, Cigna, or United?

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Thanks for the help. I have attached the document.

See the attached file.

The yellow cells are the min value of the name.

After that the min of all names. (with match).

After that the name of the min value (with indirect and adres).

See the attached file.

For the overall min() value:
regular formula in B10

Formula:

Please Login or Register  to view this content.

for the selected carriers: {Athna,Cigna and United}
Array formula. **Must be entered with Ctrl+Shift+Enter key combination.

Formula:

Please Login or Register  to view this content.

Formula to return carrier:
regular entered in C10 and copy down to C11

Formula:

Please Login or Register  to view this content.

Please see attached file

v	A	B	C	D	E	F	G	H	I	J	K
1	Monthly Active Premiums
2		Aetna	Cigna	Dean	Geisinger	Health Net	IBC	Kaiser	GHC	United	UPMC
3	Bronze Plus	1293.55	1162.53	N/A	N/A	973.17	1097.81	804.02	N/A	1372.93	N/A
4	Silver	1390.99	1184.69	N/A	N/A	1364.2	1371.97	801.99	N/A	1337	N/A
5	Gold	1744.3	1540.41	N/A	N/A	768.03	1662.91	1050.93	N/A	1679.85	N/A
6	Platinum	2004.34	1747.48	N/A	N/A	1905.34	1822.53	1162.5	N/A	1893.68	N/A
7
8
9	Lost Cost Carrier
10	Lowest rate	768.03	Health Net
11	Aetna	1162.53	Cigna
12	Cigna
13	United

Thanks for your help on this one!

Maybe you can explain if the offered solution(s), match your earlier question.

for the second question you will get better help if you add a small exel file, without confidential information.

Please don't delete the question (or change the Original question). It makes the offered solutions not usefull for other forummembers.

Hello -

This question has been modified slightly. I have a work sheet in which I need to find the lowest rate among multiple criteria.

Cell B1 is designated to provide a response of 1-22.

I'm looking to see how I can pull the lowest silver rate among Aetna, IBC or UHC if cell B1 is either 7 or 15 if it not 7 or 15 we pull the lowest rate

if cell B1 is 7 or 15 then give me the lowest rate of silver prices for Aetna and UHC.

B12=if(or($b$1=7,$b$1=15),min($b$6,$j$6),min($b$6:$k$6))

if cell B1 is 7 or 15 then give me the lowest rate of silver prices for Aetna and UHC.

B12=if(or($b$1=7,$b$1=15),min($b$6,$j$6),min($b$6:$k$6))

so the formule takes if b1=7 or b1 = 15 the lowest of Aetna (b6) and UHC (K6).

If not (so for all other options (exept 7 or 15)) it takes the overall lowest price.

What is counted wrong?

If this is not the right, formula, add an small excel file with differant options and please also add the expected result in the file (manualy)

Your explaination in your file

If B2 = 7 or 15 the rate should be 1337
If B2 is not 7 or 15 the rate should be 8011.99 (Kaiser Bronze Plus)

The formula needs to look at B1

I see no 8011.99 in the file, what i see is Kaiser 801,99 (but that is Bronze Plus).

Bronze Plus is no silver, so that looks a wrong statement to me.

A kind of confusing.

Please disregard. It seems to have worked once the 2nd array was updated. Thanks so much for your help on this one

Gold 768,03 is the over all lowest price.

That is the right answer?

Just to offer a more generally-useful version, note this ARRAY FORMULA that looks up the columns and rows instead of hard-coding them:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.

Please Login or Register  to view this content.

Like oeldere, I'm not sure what "secondary" return you want. This formula returns the lowest rate in the ENTIRE table (768.03) if B1 is NOT 7 or 15.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

@leelnich

Maybe it is not nessecary in this case, but I advice you to work with the $ signs. It avoids problems when the formula is copied (down or across).

Oeldere instead of oledere