Use Tab to insert a function into a formula
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2126: Formula AutoComplete.
Alright. So, let's say that we want to put a VLOOKUP formula in here. = V LO O K U P.
You can actually just type =VL like that, and once the formula autocomplete has only one item, then just simply press TAB, like that.
Alright, so, =VLOOKUP, we want to look up this value, , and then this is the range, this is the lookup table over here. So, I choose that whole range, CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW, and from here I want to make sure that that table array is an absolute reference, alright? I don't want it to copy down to row 14, row 15, row 16.
So, to put those $ signs in to make an absolute reference, press the F4 key like that, , 2 , FALSE, and you have your formula. Double click the fill handle to copy that down, alright?
So =VL, TAB, and then the F4 key to put the $ signs in. [ =VLOOKUP(A13,$L$13:$M$40,2,FALSE) ] The other really cool thing that started happening here, just in Office 365 somewhere during 2016, they have a new formula called TEXTJOIN or JOINTEXT, and I can never remember which one it is. So, if I just type =JOIN, alright, they'll match any characters from anywhere.
It used to be that you’d have to type =TEXTJOIN to get this, but if I just use =JOIN and then TAB, they will let me select that, and I love TEXTJOIN.
By the way, I know this is about keyboard shortcuts but TEXTJOIN is just an awesome, awesome function, lets you put commas between a whole range of cells like that, so, another shout-out to that awesome function.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Alright. So, let's say that we want to put a VLOOKUP formula in here. = V LO O K U P.
You can actually just type =VL like that, and once the formula autocomplete has only one item, then just simply press TAB, like that.
Alright, so, =VLOOKUP, we want to look up this value, , and then this is the range, this is the lookup table over here. So, I choose that whole range, CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW, and from here I want to make sure that that table array is an absolute reference, alright? I don't want it to copy down to row 14, row 15, row 16.
So, to put those $ signs in to make an absolute reference, press the F4 key like that, , 2 , FALSE, and you have your formula. Double click the fill handle to copy that down, alright?
So =VL, TAB, and then the F4 key to put the $ signs in. [ =VLOOKUP(A13,$L$13:$M$40,2,FALSE) ] The other really cool thing that started happening here, just in Office 365 somewhere during 2016, they have a new formula called TEXTJOIN or JOINTEXT, and I can never remember which one it is. So, if I just type =JOIN, alright, they'll match any characters from anywhere.
It used to be that you’d have to type =TEXTJOIN to get this, but if I just use =JOIN and then TAB, they will let me select that, and I love TEXTJOIN.
By the way, I know this is about keyboard shortcuts but TEXTJOIN is just an awesome, awesome function, lets you put commas between a whole range of cells like that, so, another shout-out to that awesome function.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
