- Computers & Software»
- Computer Software»
- Office Software Suites»
- Microsoft Office
Tutorial – MS Excel – How to Use VLookup in Microsoft Excel
Searching for a list of values present in a table in an Excel worksheet can be easily done using a simple mathematical formula. 'Vlookup' is magic!
Formula: VLookup(Lookup Value, Table Array, Column Index for lookup, Exact or approximate match)
- Lookup Value = the cell number which is to be searched in the table.
- Table Array = The table which has all the values
- Column Index for lookup = the index of the column in the table array which is to be searched
- Exact or approximate match = this argument should be TRUE for an approximate match & FALSE for an exact match.
1. Assume “Values to be searched” is the list of values you are looking for in the table. And ‘Table Column A’ and ‘Table column B’ is the table which has all the values. For e.g., you want to find the value corresponding to ‘1234abcd1’ in the table array, you can do it easily using 'Vlookup'
2. Click on the cell where you want the “found value” to appear.
3. Start typing the formula in the cell “=vlookup”. Remember, the "=" sign is as important as the function. The formula will not work without the "=" sign.
4. As you type, MS Excel will start giving you options to choose from. Select the VLOOKUP function from the options.
5. Now we have to give the first argument in the formula. Single Click on the cell which has the lookup value, in this case, ‘1234abcd1’. The cell number ‘A2’ will automatically appear in the formula. Now put a comma to type the next argument.
6. The second argument in the formula is the table array. Select the table array which has all the values. The table array cell numbers ‘C2:D24’ will automatically appear in the formula. Now put a comma to type the next argument.
7. Now, the third argument in the formula; i.e., the column index that you want to look up in the table array. In this case column index will be ‘2’ since we want to find the value corresponding to ‘1234abcd1’ in the table. Type '2' in the third argument & put a comma to type the next argument.
8. The last argument can be either true or false. This argument should be TRUE for an approximate match & FALSE for an exact match. In our case since we want an exact match and hence we are using ‘false’ as the fourth argument in the formula. Close the formula with a closing brace ‘)’ and hit enter.
9. The formula will search the value you are looking for ‘1234abcd1’ and the corresponding value ‘1’ will appear in the cell.
10. Copy & paste this formula in other cells using “CTRL+C” & “CTRL+V”. Paste formula and hit enter.
11. The formula will find all the values you are searching for in the table array.
The vlookup formula by default looks for the value in the table array starting from the row which contains the value to be looked up to the end of the table array. If you want the formula to look up the value in the entire table array for each row, then you need to tweak the formula a little and add a '$' symbol in the row & column numbers.
i.e., our formula will now look like this: VLOOKUP(A2, C$2:D$24, 2, FALSE)
More MS Excel Tutorials
- Tutorial – MS Excel – How to print comments attached to a cell
Step-by-step tutorial on how to print comments that are attached to cells, while printing a Microsoft excel file.
- Tutorial – MS Excel – How to Show/Hide/Insert comments to a cell in an excel sheet
Step-by-step tutorial on how to show/Hide/Insert comments to a cell in an excel sheet
- Tutorial – MS Excel – How to freeze a column or row in Microsoft excel
Step-by-step tutorial to freeze columns/rows/panes in Microsoft excel.
- Tutorial – MS Excel – How to insert a picture in a Microsoft excel worksheet
Step-by-step tutorial to insert a picture in a Microsoft excel worksheet.
- Tutorial – MS Excel – How to embed a file as an object in a Microsoft excel worksheet
Step-by-step tutorial to embed a file as an object in a Microsoft excel worksheet
- Tutorial – MS Excel – How to combine values from two or more columns into a single column
Step-by-step tutorial to combine 2 or more column values into a single column in a Microsoft excel worksheet.
- Tutorial – MS Excel – How to highlight duplicate values in Microsoft excel without deleting them
Sometimes you may not want to delete the duplicate values but may want to highlight them anyway. Here's a step-by-step tutorial to highlight duplicate values in Microsoft excel without deleting them.
- Tutorial - MS Excel - How to remove duplicate values from an excel sheet
Step-by-step tutorial to remove duplicate values from a Microsoft excel worksheet.
- Tutorial – MS Excel – How to write text vertically or at an angle in an Excel sheet
This hub describes ways to be able to type text vertically or at an angle in a Microsoft Excel Sheet.
© 2013 Petite Hubpages Fanatic