Excel Magic Trick 1374: Extract Only Total Row: VLOOKUP & Dynamic Range Created by INDEX Function
HTML-код
- Опубликовано: 8 сен 2024
- Download File: people.highline...
Create Shrinking and Expanding Range with INDEX function. See how to Get the Total for each employee when the Employee Name column has many empty cells and the “TOTAL” is in a row without an employee name. See how to do this with VLOOKUP & a Dynamic Range Created by INDEX Function. This amazing formula comes from Bill Szysz at RUclips.
Reference Video:
Excel Magic Trick 1372: Extract Only Total Row For Each Name: Formula or Power Query (6 Examples)
• Excel Magic Trick 1372...
You never cease to amaze excel followers. If i had options, i would hit the like button zillion times. Thanks sir.
You are welcome! Thanks for the Zillion Likes : )
Amazing solution! just wanted to share that the day after I watched the previous video with the other solutions a colleague at work asked for help on a problem that was exactly the same. I was able to help her right away and look extremely excel knowledgeable because of what I had learned from you the day before. Thank you do much for all that you share: your knowledge, your patience, your time.
I love to hear that! You are welcome for the videos and thank you for studying hard, assimilating the Excel knowledge and helping others to become good with Excel and have fun doing it!
Thanks Bill & Mike. Very neat solution! I'm a big fan of dynamic ranges with Index.
You are welcome! We do have a great Online Excel Team : )
i have watched 2-3 videos but sir you're amazing & very kind person because your videos always help us without any return
GOD bless you always
I am happy to hear that the videos help you!!!
Beautiful, Mike!!!! You and Bill are amazing.
Yes, thanks Bill Szysz : )
@John Borg Thanks :-)
You can explain always and everything in excel in easy language. Incredible!!!
I always admire this easiness. Thanks Mike :-)
Thank you, O Wise Excel Master!!! : )
Thanks Bill and Mike. That was awesome.
Glad it helps, Fellow Teacher : )
@@excelisfun it always does fellow teacher.
Hey Mike, I just came here to say you "Hi!" You rule :)
I am glad that you enjoy the videos! : )
Absolutely brilliant 👍 Super Smart. Great tricks Mike 👏
: ) : )
Exceptionally Clever. Very nice.
It really is elegant!!! Thanks Bill Szysz : )
Super clever stuff...great video Mike...thanks.
Glad you like the video!
Excellent thank you! I am trying to extend the example by using structured references (Success!) and replacing the absolute reference $C$23 (the end of the range) with a dynamic refernce (failed)
The formula resolves properly to $C$23 when placed in its own cell, but not when included in the range for vlookup
The addition is =ADDRESS(ROW(Table1[Amount])+ROWS(Table1[Amount])-1,COLUMN(Table1[Amount])+COLUMNS(Table1[Amount])-1)
which makes the final formula for F9
=VLOOKUP("TOTAL",INDEX(Table1[Category],MATCH(E4,Table1[Name],0)):ADDRESS(ROW(Table1[Amount])+ROWS(Table1[Amount])-1,COLUMN(Table1[Amount])+COLUMNS(Table1[Amount])-1),2,0)
Why is this failing? Please help
Try:
=VLOOKUP("TOTAL",INDEX(TotalTable[Category/Total],MATCH(E9,TotalTable[Name],0)):INDEX(TotalTable[Amount/Total],MATCH(9.9E+307,TotalTable[Amount/Total])),2,0)
Where we are always looking for last cell in Amount/Total Column using the "Big Number" concept and Approximate Match Lookup.
You can also enable the Total Row option for your Table1 (should delete formulas and text in it) and use it as a pointer. This is simple trick and sometimes is enough :-)
=VLOOKUP("TOTAL",INDEX(Table1[Category],MATCH(E4,Table1[Name],0)) : Table1[[#Totals],[Amount]],2,0)
If it is not enough, then use Mike's method
I ended up going with Bill Szysz 's solution below about adding the total row. While both your big number matching approach and my address() approach resolved properly in an independent cell...they did not when present in the table_array argument of vlookup.
Thanks again for the worksheet both ExcellsFun and Bill Szysz, I am definitely keeping this one around for reuse in the future.
Wow! That is quite a trick! Thanks for the hot tip : )
I am not having the same trouble with the table_array argument. Both of these formulas seem to be working when I add new records to the Excel Table:
Big Number: =VLOOKUP("TOTAL",INDEX(TotalsTable[Category/Total],MATCH(E13,TotalsTable[Name],0)):INDEX(TotalsTable[Amount/Total],MATCH(9.9E+307,TotalsTable[Amount/Total])),2,0)
ROWS in INDEX: =VLOOKUP("TOTAL",INDEX(TotalsTable[Category/Total],MATCH(E13,TotalsTable[Name],0)):INDEX(TotalsTable[Amount/Total],ROWS(TotalsTable[Amount/Total])),2,0)
I am confused by why you are getting an error.
However, I must say, if you can have an empty Totals Row, this sure is nice: =VLOOKUP("TOTAL",INDEX(TotalsTable[Category/Total],MATCH(E13,TotalsTable[Name],0)):TotalsTable[[#Totals],[Amount/Total]],2,0)
Brilliant Video 📹 👏
Totally awesome. Thanks!
You are welcome! It is great to hanging out on such an awesome Online Excel Team!!!
very effective.. thanks a ton mike
You are welcome a ton! Thanks to Bill Szysz too : )
first view and like from me
Thanks for the Support and Speed in getting to the video first!!
Simple and beautiful
: )
Simply amazing
Glad you liked it!
amazing. 🤗
Glad you like it!
Thank You
Excellent!!! :)
EXCELlent : )
Amazing :)
Yes, it is, so is Bill Szysz : )
Hi Mike, great video! I noticed though that you are working with a list that has the word "total" hard-coded on each row as well as the total value, what if you needed to subtotal first using the subtotal built-in function and you wanted excel to vlookup and return the subtotal for each of those names on a separated table? what would the formula look like? It would really help me to know how to do that! thank you.
hi there, i have a question regarding the firmula that I'm trying to understand learn and apply to a different column range but can't seem to solve it will you be able to helo me point at a right direction please? p.s amazing tutorials.
Greattttttttttttttttttttttttttttttttttttttttttttttt video trick
Glad you like it!
Holy shit!:)
i have a little problem.. i have thousand number in every cell..the number like 326598, 124578, i want to put starting number 6 at every single cell.. so, all the number change to 6326598, 6124578.. please help me solve this.. tq..