Getting VLOOKUP to return multiple values

This post is for the somewhat more advanced Excel user.

You may already be familiar with the VLOOKUP function, what you may not know is that you can get a single VLOOKUP formula to return multiple values by using an array.

1. Select each cell that you want to contain a result from the VLOOKUP.  In the screenshot below, the cells I4:K4 were selected
2. Switch to the Formulas tab, select Lookup&Reference, VLOOKUP
3. Specify the Lookup_value and Table_array as normal
4. Enter a column number for each column that you want data returned from.  Separate the column numbers by commas and enclose it all in braces / curly brackets {  }.  The column numbers can be specified in any order.  In the example below, data will be returned from columns {5,6,3}
5. Specify your Range_lookup requirements
6. Press CTRL, SHIFT, ENTER

Hope this is a time saver for you!  Please share if you think anyone might find this useful too.

Restrict values using Data Validation lists

Using the Data Validation feature in Excel you are able to restrict entry to a predefined list of values.  

In many cases spreadsheets are completed by more than one user which often results in different styles of inputting data.  By having a drop down list to select from ensures consistency which not only makes for a neater appearance, but also significantly improves the ease of use of tools such as Pivot Tables and Filters.

• Somewhere in your spreadsheet, type out the list of values that you would like to use as the source for your drop down list 
• Select the cells to which you would like to add Data Validation drop down list
• On the Data tab, select Data Validation. (A drop-down menu will appear.)
• Select Data Validation.
• Select List from the Allow drop down.
• In the Source field, select the range of cells that will act as the source data for the drop down list

• Click OK and begin using the drop down list to aid data entry

Happy spread-sheeting! Please share if you found this useful.

Easy way to wrap text in MS Excel

When you want to split your heading over more than one line but within the same cell, you may be familiar with the Wrap Text button on the ribbon.  However there is a quicker method that can be implemented as you are typing: ALT Enter.

Using the example below, type Stock then press ALT ENTER to move your cursor into the next line, then type MVMT and press ALT ENTER to move down again and then type Boxes.  Press ENTER when you are done.

You can even edit a cell afterwards to add a line break if necessary, you'd just have to press ALT ENTER at the appropriate spot in the formula bar (or in the cell itself if you're in edit mode).

Please share this using one of the buttons below if you think others will find this useful, or leave me a comment if there is something else you'd like to learn about.