This is a simple tutorial in just one of many easily overlooked Microsoft Excel functions. If you find lots of gaps like these in your Excel knowledge then one of our tailor-made Excel training courses will be just what you need. We have public courses in Beginners Excel Training and Advanced Excel Training, and we can also tailor a private course to suit the individual need of you or your business.
VLOOKUP and HLOOKUP allow you to search a data range for any value entered, and return information or a value based on that data. VLOOKUP allows you to search information formatted by column, HLOOKUP for information formatted by row.
For example, your primary school can use the function to create a vast spreadsheet of pupils’ test scores and their relevant grades/comments. This would be useful for grading ‘on the curve’ – if you change the grade relating to each mark out of 10, you only have to do it once rather than for each pupil.
Step 1: Begin with the basic table – possible marks, grades and comments.
Step 2: Select this table (without headings) and enter a title into the name box.
Step 3: Create a new table – in this case of pupil names and marks.
Step 4: Select the cell you want to enter the function in.
This is the formula you’ll be using:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
For tables that have been formatted by row, simply use the following formula instead =HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
Step 5: The lookup_value is the value you want the spreadsheet to read, in this case cell B18.
Step 6: Add a comma, then the table_array (table name)
Step 7: Add a comma, then the col_index_num – which column of data you want, so in this case column 2
Step 8: Add a comma, then the range_lookup – either “true” or “false”; where “true” means to use information close to it, “false” means use only exact match data.
Close your bracket.
Step 9: Press enter and the information will be retrieved
Step 10: Repeat for any other columns of information you require
Step 11: Highlight and drag the cells down to copy the formula for the rest of the list.
Read more of our recent Microsoft Excel tutorials
- How to Create Drop-Down Lists in Excel
- Excel Scenarios Tutorial
- Pivot Table Slicers in Excel 2010
- Excel Function Keyboard Shortcuts