You are here:

Excel/Reeturn the Header of the Named Range based on Logical Test



I have some Data and a header for that range.
It is a named range and the Header itself is the Name of the Range A2:A7.

   A     B
1 HHHHH          
2 FALSE          
3 FALSE          
4 TRUE    
5 FALSE          
6 FALSE          
7 FALSE   

I want to return the header of the range if value is TRUE.
So in B4, output should be HHHHH (Which the header of the range.)

I don't want to use A:A or A1:A7 in formula.
I want to catch the header just based on the cell which contains TRUE.

Can you help?

ANSWER: Parantap,

B2:  =if(A2,$A$1,"")
then drag fill down column B next to your data.

Since you are making a decision on the value in only one cell, then you only need to reference that cell  (A2 in the example).

As you drag fill down, the A2 will change to A3 in cell B3,  A4 in cell B4 and so forth (Relative Cell Reference).

The reference to A1 is fixed by writing it as $A$1 so that will not change (absolute cell reference)  

Hopefully that answers what you are asking.  If not, then follow up with additional information that will clarify your request.

Tom Ogilvy

---------- FOLLOW-UP ----------


Thanks, Yes it gives me the result.
But as I specified, I want to catch the header just based on that relative reference.

Instead of $A$1, I want to set formula which refers "the Top row of the range - which is the Name of that Range", in which A2 is situated.

For example, =if(A2=true,index(RangeName,1,1)) this will give me the Value; contained in the first row of the Range right?

But there is nothing like =index(RangeName,-1,1) or =index(RangeName,0,1) !!
If that was possible then I could have the Header as a result.
So is there any alternative which does the same?


ANSWER: Parantap,

INDEX won't do that but OFFSET will


Returns the value in the row above the named range HHHH

B2:  =if(A2,OFFSET(HHHH,-1,0,1,1),"")

Tom Ogilvy

---------- FOLLOW-UP ----------

I realized that even in this solution (and the one I suggested), I have to enter the name of the range in the formula; Which I don't want to do; and that is why I need formula.

I want to catch the header just based on that relative reference.

I don't want to type Header (or say Name of the Range) in the formula.

In Short, I want to do this -


Yes, This is exactly what I want.



There isn't any built in function that will give you that information.  An individual cell could be part of any number of names but even if it was in one named range, there is no function that will give you that information.  The CELL function is the function that can return information about a cell, but name of namedrange membership is not one of the things it does.   

Even VBA does not have a property that will tell you that.  You would have to loop through all the named ranges and test if the cell in question is a member of that named range.

Pursing an array formula would pretty much require checking every named range but that would require having prior knowledge of all the named ranges and recording them in a formula.  

So I don't see a built in function solution for this problem.  

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.