Forum Discussion
Using names in formulas?
Hello,
I have the following table:
| Items | Jan | Feb | Mar |
| Apples | 7 | 2 | 3 |
| Bananas | 12 | 25 | 14 |
| Oranges | 10 | 8 | 6 |
What is the best way to use name ranges, so when I want to extract for example # bananas in Feb, I don't have to use Vlookup?
I tried Creating Names from selection, but it always shows all months for banans, not only 25, but 12;25;14.
Thanks for your help.
9 Replies
Assuming you have successfully defined the rows Apples, Bananas, Oranges and the column ranges Jan, Feb, Mar, then the value for a given month is obtained by intersecting a row and column range using a space as the operator. Examples are
25 = (Bananas Feb) 27 = SUM(Apples:Bananas Feb) 10 = SUM(Apples Feb, Oranges Feb)Life gets a little more complicated if you want to select months because the Names can get muddled with the A1-style column references
#NULL! = Apples FEB:JAN {7, 2} = Apples TAKE(Items, ,2)- Thanks, Peter
this seems to work as long as the left column has words in each row. If I replace the words apples bananas and oranages with 1,2,3 it doesn't work. Any idea if it is possible to work with numbers?
Thanks againIt is just that naming from selection doesn't work because a name must start with an underscore or a letter. In addition it must not be a valid cell reference. Name manager and the Name box offer alternative ways of defining names. For example
= (January _1st)is a valid intersection assuming the names are defined appropriately.
From what I understand you want a non-VLOOKUP solution approach.
Here is a very simple solution without VLOOKUP.
=INDEX(C3:C5,MATCH("Bananas",A3:A5,0))
I hope this helps!
- Thanks,
I have explored the index variant. The reality is that my table is quite large >1000 rows and I also have a few formulas in use. So I am really looking for something that if the month is Feb, I would like to type something like bananas+apples and get 27.
I was just wondering if this can be done using name manager, otherwise, I can use index or vlookups.
Thanks againWell, if there are so many, then a quick filter...is the quickest way 🙂
Example added with Filter and SUBTOTAL function.
Of course you can use the Name Manager too, to define names for cells or ranges of cells and then use those names in formulas. For example, you could define a name for the cell that contains the value for bananas sold in February and another name for the cell that contains the value for apples sold in February. Then you could use those names in a formula to add the two values together.
To do this, first select the cell that contains the value for bananas sold in February. Then click on the "Formulas" tab in the ribbon and click on "Name Manager". In the Name Manager window, click on "New" and enter a name for the cell, such as "Bananas_Feb". Repeat this process to define a name for the cell that contains the value for apples sold in February.
Once you have defined these names, you can use them in a formula to add the two values together. For example: `=Bananas_Feb + Apples_Feb`. This formula will return the sum of the values in the cells that you defined with the names "Bananas_Feb" and "Apples_Feb".
