Last 50 Posts on Chandoo.org

omer on 2019-08-24 14:12:52
AQ43- macbook

dheeraj on 2019-08-24 05:50:51
hi chandoo sir my name is dheeraj nd i m from india, ofcourse i calk u sir i want to know about vlookup and average so i would like to know find average. regards Dheeraj

David N on 2019-08-24 00:01:19
Chandoo, in your closing bullets, for the one about sorting data, you forgot an INDEX in your example formula. Your MATCH for Jessica will never be Jessica, it will be the numerical position of Jessica. So you need to wrap it in an INDEX that also references the Sales[Sales Person] column. As for VLOOKUP versus INDEX-MATCH, I will join the chorus of those who use only the latter for all the advantages they (and you) have already noted and then some. I have not used VLOOKUP in at least a decade, and I will go to my grave encouraging others to throw it in the trash and never look back. I even tell people to consider it to be a cuss word that should be purged from our collective functional vocabulary. Knowing both ways is one thing, because there will always be that file you inherit from someone who used VLOOKUP. But actually using both is like saying you know how to write a book both by hand with a pen and by computer with your fingertips and then saying you prefer the ink pen because it's harder?!? Because it's a nostalgic trip down memory lane...maybe...but never because it's the better or easier choice. The pen will take longer (on average), and correcting mistakes will be a nightmare, while the computer is faster (on average) with corrections being a breeze. INDEX-MATCH can do literally everything VLOOKUP can even dream of and more, so let's move on and never cuss again.

Chandoo on 2019-08-23 18:54:41
Hi Abhi, Have you tried using Pivot Tables and slicers for this? They might be easier than SUMIFS and you don't need to write any formulas. See this for an intro to slicers - Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Bhuvan on 2019-08-23 14:06:30
I thought earlier that I know most of about Excel. But after coming to this website, I realised that I know very little about Excel. So much to learn from you in Excel. Thank you.

Krishna Solanki on 2019-08-23 13:55:48
I am looking for spread sheet for ULIP Funds. Please share if you have related to ULIP fund.

Abhi on 2019-08-23 12:24:07
Hi All, I have a unique problem with sumifs . I have 5 conditions/parameters for sumifs , out of which 3 conditions might have 1 / multiple selections (values coming from user selected multi list box and falling in different cells ) .ALL Conditions are Character variable . In this case i have sum up spends value using sumifs for all possible combinations user will select from the drop down menu and i have to keep the parametrs automated (cell referenced - as they are user defined ) and i cannot hardcode them using {} . How can i do it using SUMIFS . Regards Abhishek B

YASIR IFTEKHAR on 2019-08-23 06:33:37
HI CHANDOO, I was trying to make my own sheet( with the help of your sheet), after importing data, how to arrange the sheet, there are a lot of unnecessary rows. manually its task to delete the row.

Michael on 2019-08-23 06:23:58
I use vlookup mostly when I am working with simple and clean data, INDEX + Match when I am working with external connections that require complex and dynamic formulas

Varun Sharma on 2019-08-23 05:07:10
Excel looks simple but it is very vast. You must know formulas and every single tab and their functioning. Thank you so much, Chandoo.

chatp Tun on 2019-08-23 03:06:19
you make the awesome filter as I need thank you so much, chandoo

Chari on 2019-08-23 02:52:41
Index + Match always

Dorothy A on 2019-08-22 18:39:22
I am with Mark Knochel. I use Index match exclusively because it does everything VLookup does + more.

Jim van Oss on 2019-08-22 16:03:06
I always use Index+match as Vlookup/Hlookup are too quirky to deal with. Index+match always works the same way and are particularity useful with Tables.

Dana on 2019-08-22 16:01:16
It depends on the report. If it's something that needs to be once, I'll use VLookup or Index+Match (VLookup for quick-and-dirty things, Index+Match for more complex things). If it's something that will need to be re-run on a regular basis, I'll use PowerQuery.

Rudy Salinas on 2019-08-22 15:49:20
Since I am beginner here; I would prefer vlookup for now...

Hui... on 2019-08-22 15:03:48
@Francis Have a read at: https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples

Francis on 2019-08-22 11:27:52
Please I need someone to help me shuffle numbers from 1,2,3... 90. I mean help Mr shuffle numbers from 1 to 90 and each line must contain 5 numbers... Example, if I am to shuffle numbers from 1 to 5 and each line must contain 3 numbers, I would have (1,2,3), (1,2,4), (1,2,5), (1,3,4), (1,3,5) (1,4,5) (2,3,4) (2,3,5) (2,4,5) (3,4,5)

Mark F. on 2019-08-21 10:25:09
I tried to download this Issue Tracking Template for both Excel 2007 and later and the older version. However, the One drive folder showed Empty. Please assist! I am of need of this kind of template for my work. Regards, Mark

Yamin on 2019-08-21 09:28:18
how can we make spaces in the reesult e.g 25 655 2335

James on 2019-08-21 09:18:35
Thanks Drew. This article is old but interesting nonetheless. Having the ability to create PowerPoint presentation automatically using VBA has saved us countless hours in manually creating slides for frequently updated reports and dashboards.

Yamin on 2019-08-21 08:29:55
HI, How can we add spaces between numbers and removing decimals.

Chandoo on 2019-08-21 08:04:23
@Rajesh.. thanks for the purchase. Masterclass lessons are just videos that you download. There is no login page.

Chandoo on 2019-08-21 08:03:53
Hmm, not sure why. You can also right click on the labels and choose "Format" to format them.

Simon C. on 2019-08-21 07:40:02
Try to get this to work... =RANK(($C4&RANK($D4,$D$4:$D$7,1)&RANK($E4,$E$4:$E$7,1))*1,($C4:$C7&RANK($D4:$D7,$D$4:$D$7,1)&RANK($E4:$E7,$E$4:$E$7,1))*1,0) Can somebody please help, thank you.

Arun on 2019-08-21 07:07:08
Thanks a Lot...

Arindam on 2019-08-20 13:21:00
Hi Chandoo, Ctrl+1 is not opening the dialog box for formatting for the dat label in Excel 2016. Do you have any work around? Arindam

Rajesh on 2019-08-20 03:15:36
Chandoo Bhai I purchased course. I am finding it difficult to get login page. Please add some link on home page. I love your courses. Regards Rajesh

Kuldeep on 2019-08-19 08:15:00
Simply Awesome....

Samehadaku on 2019-08-19 01:01:55
ah nice use of formule there