Fantastic! Was barely familiar with the CHOOSE function until I saw this video. You've motivated me to replace nested IFs with CHOOSE as well as to explore other uses of this versatile function in my daily work. Thanks a ton!
The choose function is a great way to get rid of the volatile function "Indirekt" that many people use to dynamically reference to different sheets which is not possible with Index-Match or Vlookup
A great example of this is used to determine various holidays that fall always on specific days, i.e. Mother's day is always the 2nd Sunday in May. You can use: =DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1) where the year is in cell A3 on a spreadsheet. The formula breaks out to =(Date(May 1st, [year entered in A3]) + (7days) + (the number returned from determining the Weekday of May 1st as the index, and the value [0,6,5,4,3,2,1]). i.e. in 2020, the Second Sunday is May 10th. May first, plus 7 days is always May 8th. May first falls on a Friday, so the WEEKDAY portion returns a 6. This is how the Index Number is determined. We want to return the 6th value from the list... The Choose Function has a value list of 0,6,5,4,3,2,1 -- the 6th value is 2. Thus: May 1st is a Friday, Plus 7 Days is Friday May 8th, Plus 2 more days gets you to May 10th, the second Sunday of the month for 2020. To Test this, set up a sheet, enter the year only in Cell A3, then copy/paste this formula into another cell.
Can this work with Index(array,Match()) to let you know if you have multiple values that are the same, or like a VLookup if you want to look up the 2'nd or 3'rd occasion the look up value occurs? I recently told someone in an interview that the most complicated formula I've ever written was a nested if Formula. Obviously he thought I had no idea what I was talking about but you do! You mentioned something about it being able to help in this way in the beginning of the video. I'm an accountant and have to reconcile a lot of data and let's say you are doing payroll for thousands of employees so you are bound to have employees who get paid the same amount. I use the index match (with a double match function instead of just one [match (1,(A1:A500=D1)*(B1:B500=E1)]. It turns the formula into an array and then I have to do it for the other set of data to create references. I then go and find duplicates because it only gives you the first match and manually change those and finish it off by using one reference as the master file and creating a VLookup to a whole new generated set of numbers to create the true tick marks..... If I can get this to count the number of times an occurrence shows up in the data and put a .1 or .2 after the original reference number from the index match I wouldn't need to do the manual part or anything after that. I hope you were able to follow along. I'm a nerd, if you read this thanks for your time!!!!!
I think you would use Vlookup to determine the potential Index Number which would point you to the proper value in the value set you determine in the secondary parts of your arguments.
Hi, i have dought on hyperlink, i will use concetate fonction using in herperlink, colums have same file name, i will use above function to view the listing link in another colums , it's working, but i cat link sub folders, give any idea, =Hyperlink(contecate("//file path/",file name or cell value,"file type"),"friendly name"))this formula i have using, pls give any suggestions for sub folders
Fantastic! Was barely familiar with the CHOOSE function until I saw this video. You've motivated me to replace nested IFs with CHOOSE as well as to explore other uses of this versatile function in my daily work. Thanks a ton!
Damn where you been my whole life, just found this channel. Great stuff!
Just subbed. Great tutorial. Clear and concise. Thank you.
Great video. Now I can imagine its many practical uses in my work!! Thank you!!
Superb dear. Awesome method of explanation. 👍 From India
I've seen a couple of vids but this one made me sub. Great job!
The choose function is a great way to get rid of the volatile function "Indirekt" that many people use to dynamically reference to different sheets which is not possible with Index-Match or Vlookup
Thanks for the video. To make it very clear, it wlould be good to show also what t would have been wth IF fuction
Exccelent tip! I have never used this one, it's a lot helpful!!!
A great example of this is used to determine various holidays that fall always on specific days, i.e. Mother's day is always the 2nd Sunday in May. You can use: =DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1) where the year is in cell A3 on a spreadsheet.
The formula breaks out to =(Date(May 1st, [year entered in A3]) + (7days) + (the number returned from determining the Weekday of May 1st as the index, and the value [0,6,5,4,3,2,1]).
i.e. in 2020, the Second Sunday is May 10th.
May first, plus 7 days is always May 8th.
May first falls on a Friday, so the WEEKDAY portion returns a 6. This is how the Index Number is determined. We want to return the 6th value from the list...
The Choose Function has a value list of 0,6,5,4,3,2,1 -- the 6th value is 2.
Thus: May 1st is a Friday, Plus 7 Days is Friday May 8th, Plus 2 more days gets you to May 10th, the second Sunday of the month for 2020.
To Test this, set up a sheet, enter the year only in Cell A3, then copy/paste this formula into another cell.
The real heroes are in the comments
Thanks! Haven't used it yet. I'll probably stick to the trusted Index/Match combo with a named range or a table reference, though.
Very well explained! Thank you so much.
Thank for the video..👌👌👍🏻❤ its great😊
so it is array indexing? for an array of strings?
Great tutorial! Easy to understand.
For most of these examples, it would be better to use the SWITCH() function. But but very interesting anyway as I didn’t know that CHOOSE() existed.
Nicely explained tutorial sir
Good stuff, thanks!
Can this work with Index(array,Match()) to let you know if you have multiple values that are the same, or like a VLookup if you want to look up the 2'nd or 3'rd occasion the look up value occurs? I recently told someone in an interview that the most complicated formula I've ever written was a nested if Formula. Obviously he thought I had no idea what I was talking about but you do! You mentioned something about it being able to help in this way in the beginning of the video. I'm an accountant and have to reconcile a lot of data and let's say you are doing payroll for thousands of employees so you are bound to have employees who get paid the same amount. I use the index match (with a double match function instead of just one [match (1,(A1:A500=D1)*(B1:B500=E1)]. It turns the formula into an array and then I have to do it for the other set of data to create references. I then go and find duplicates because it only gives you the first match and manually change those and finish it off by using one reference as the master file and creating a VLookup to a whole new generated set of numbers to create the true tick marks..... If I can get this to count the number of times an occurrence shows up in the data and put a .1 or .2 after the original reference number from the index match I wouldn't need to do the manual part or anything after that. I hope you were able to follow along. I'm a nerd, if you read this thanks for your time!!!!!
The last 'complicated' example is more easily done and way more flexible using a pivot table.
I like your video but still dont get the use of choose function that you couldnt solve kind of easier and with less harded coded stuff with vlookup
If you use Choose with True/False, isn't it easier to simply use If?
Spring stars in February!
Hi! Can u explain choos function with vlookup criteria
I think you would use Vlookup to determine the potential Index Number which would point you to the proper value in the value set you determine in the secondary parts of your arguments.
Google the reverse look up which will use VLOOKUP concept
value := array[1]
Hi, i have dought on hyperlink, i will use concetate fonction using in herperlink, colums have same file name, i will use above function to view the listing link in another colums , it's working, but i cat link sub folders, give any idea,
=Hyperlink(contecate("//file path/",file name or cell value,"file type"),"friendly name"))this formula i have using, pls give any suggestions for sub folders
Good insight However I would have preferred to have the examples in different sheets, as the one you presented is too crowdedd and distracting.
Are you Ryan Reynolds?
Xx
Choose with a logic function seems no different than an IF statement.