Forum Discussion
Using names in formulas?
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!
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 again
Well, 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".
- Thank you for your reply.
Unfortunately, it is not feasible to individually name a few hundred cells:)Then you are probably applying Names at too fine a level of granularity, I tend to apply a name to ranges at as course a level as possible and then pick out individual records by formula (typically XLOOKUP or BYROW).
For the moment, though, let's assume you wish to name individual records. Rather than using the numeric sequence numbers on the left, write or generate a text sequence to the right. Use that to name the rows, and then you may delete the text if you so wish.
