Formula to return column header

btadams

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,943
I have a table of data and need a formula to find the minimum in that row and return the text that appears in that column's header. For example say the min occurs in the 3rd column of a named range, I want the text that appears in the first cell of that 3rd column. Thanks,
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
=INDEX(B1:E1,MATCH(MIN(B2:E2),B2:E2,0))

where B1:E1 houses column headings/labels and B2:E2 is target row for which MIN is comouted.
 
Upvote 0
On 2003-02-06 16:51, PaddyD wrote:
...what about 2 instances of the minimum value?
Click to expand...

You'll get the header that corresponds to the first/leftmost instance of the computed MIN.
This message was edited by Aladin Akyurek on 2003-02-06 16:58
 
Upvote 0
Aladin,

So simple & beautiful. I was creating some ungodly monster involving hlookup + vlookup + offset + address functions. Thank you
 
Upvote 0
Hi aladin - the Q was more directed to the OP - as in "What do you want to happen if there are 2 instances...." :)
 
Upvote 0
On 2003-02-06 17:57, PaddyD wrote:
Hi aladin - the Q was more directed to the OP - as in "What do you want to happen if there are 2 instances...." :)
Click to expand...

I know. I sometimes omit the qualification with such MIN or MAX questions. Your question provided the occasion to add it.
 
Upvote 0
This topic was great for me, I wonder if you can tell me how to fill this formula Down and still retain the reference to the header line. I have 10 tables with 40 rows and i want to return the supplier name?

Regards

David
 
Upvote 0
drogers0 said:
This topic was great for me, I wonder if you can tell me how to fill this formula Down and still retain the reference to the header line. I have 10 tables with 40 rows and i want to return the supplier name?

Regards

David
Click to expand...

I guess the header row consists of suppliers, the first column of products, and the prices in the body of the table. Right?
 
Upvote 0
drogers0 said:
This topic was great for me, I wonder if you can tell me how to fill this formula Down and still retain the reference to the header line. I have 10 tables with 40 rows and i want to return the supplier name?

Regards

David
Click to expand...

To do this just place $ in the header reference cells. Such as =INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0))

Thanks for this formula. Very helpful and simpler than my attempt. I knew Index would be useful here but couldn't visualize the formula. I've used a MAX rather than MIN but logic is identical.
 
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
318
Nathan Asius
N
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
427
MissDB101
M
Z
  • Question
Count All values in every 3rd column
Replies
4
Views
641
Peter_SSs
Peter_SSs
J
  • Question
Trying to return text from a column on different sheet and getting #value error.
2
Replies
10
Views
208
Alex Blakenburg
A
B
  • Question
Auto calculation of Percentage, Position and Grade
Replies
1
Views
329
awoohaw
awoohaw

Forum statistics

Threads
1,214,591
Messages
6,120,426
Members
448,961
Latest member
nzskater

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