I like how you explain everything in detail, totally needed it.

2nd method you exolained is so so cool. i was looking for something like this from last 6 months

Ty so much for this! You saved my bacon. I do not like index formulas, this is a great solution.

This is absolutely amazing, thanks alot bro.

@TeachExcel

5 жыл бұрын

You're very welcome :)

This worked perfectly! I see that you don’t do much in power query, but do you know how to do this in power query? My dataset is too big to have the formula in excel.

Nice one and thank you for your detail explaination.

@TeachExcel

5 жыл бұрын

You're welcome! Glad you like it)

Awesome tutorial, how to get the formula fully dynamic, by replacing the *G10* with a fórmula to return 1, 2, 3, ......

Thanks , its useful

Amazing and very informative video. I really enjoy it. Thanks for sharing this

@TeachExcel

3 жыл бұрын

You are very welcome! I'm glad to hear it :)

Thanks for this EXCELlent video

@TeachExcel

5 жыл бұрын

hahaha you're welcome)

Thanks for this tutorial.. very instructive. Note that you can avoid using CSE if you use the AGGREGATE function, as follows: =INDEX($E$2:$E$13,AGGREGATE(15,6,(1/($A$2:$A$13=$H$9))*((ROW($A$2:$A$13)-ROW($A$1))),G10)) - copy down, as with your formula in the video.. Function arguments 14 through 19 in AGGREGATE are programmed to handle array operations, so you can use function_num 15 for SMALL. You then use function options 6 to ignore error values. Then use the divide by 1 trick on the logical test of product match to force a #DIV/0! error in the returned array to remove the 0's. The resulting array when multiplied by the ROW range (less the first row) gives you the array of matching rows with the #DIV/0! errors that are then ignored by AGGREGATE because of options 6. Then, use the k argument like in SMALL to return the desired ordinal match by referencing your figures in column I. AGGREGATE saves you from using CSE which can cause the issues you described with co-workers or even yourself forgetting to press CSE on subsequent edits. Soon with the new EXCEL calc engine and the FILTER dynamic array function, this kind of thing will be much easier. Until then.. AGGREGATE is another way to solve the problem without needing CSE. Thanks again for your videos which are great learning resources and they also challenge me to think and expand my knowledge. Thumbs up!!

@alokghosh9311

4 жыл бұрын

Nice...

I just used the first part of this video to make a helper column. The rest of it is able to be done with Xlookup. I wanted to return "X" word from a list of tweets. Each tweet was in a row and each word was in individual cells but with over 10,000 tweets impossible to do manual. I wanted to return the full tweets from any word that had "x" to do a some what manual analysis.

Excellent

HERO

What if I want the max value in the list for a product and not the value in the end of the list

what if the first column is employee number ?

it not working with number ?

What If I have 25,000 rows of data? This solution does NOT scale :(

@TeachExcel

4 жыл бұрын

Well, without being able to test right now, I'd say try an index/match solution or just use a macro - if you ask in our forum, and include sample data, it will be much easier to help. www.teachexcel.com/talk/microsoft-office?src=yt_comment

Nice one, thanks. Nevertheless, speaking of last 3 sales, the formula indeed returns the first n occurrences of the product in the list, which means the result depends on the list sorting. I think the formula should rather return the last 3 sales chronologically, according the date, independently of the position in the list, shouldn't it? :) If this is your case and you prefer the formula to return the sales from January 10th for param 1 ($ 6), January 7th for param 2 ($ 30) and January 4th for param 3 ($ 3), then this slightly modified solution might be what you are looking for: {=INDEX($E$2:$E$13,MATCH(LARGE(IF($H$9=$A$2:$A$13,$B$2:$B$13,""),G10),$B$2:$B$13,0))} Warning - only works if the Product/Date combination is unique (ie. the product sales from certain date is aggregated on one row only, resp. no multiple rows with the same date for one product are allowed)

@TeachExcel

5 жыл бұрын

Thanks for the comment and additional info! There are so many ways to do things in Excel and your formula looks pretty cool! I did mention the sorting topic toward the end of the tutorial btw, :P

End of list =INDEX($E$2:$E$13,MAX(ROW($E$2:$E$13)-(ROW($E$2)-1))) Start of List =INDEX($E$2:$E$13,MIN(ROW($E$2:$E$13)-(ROW($E$2)-1)))

@TeachExcel

3 жыл бұрын

Thanks for the additional example formulas! So many ways to do something in Excel! :)

I felt nauseas. You're tutorial is so good, but I don't understand.

Disappointed, I expected more.

@TeachExcel

5 жыл бұрын

Like what? If you only complain, then there is no way to help...

@ConsulthinkProgrammer

3 жыл бұрын

You can improve related trick in google sheets

@b-espinarrobertjustin4012

3 жыл бұрын

@@TeachExcelOh my. You're a roaster too. Hahaha

Too complicated and time-consuming. Just use the Advanced filter, problem solved in 1 minute!