Excel Shortcuts - Formula AutoComplete

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.