Neha is a software professional with 13+ years of experience in the IT Industry. She enjoys writing technical tutorials.
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. Sample Table: Column A and Column B
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. Select the cell to apply the formula
Click on the cell where you want the “found value” to appear.
3. Construct the formula for vlookup
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. Vlookup parameters start showing up automatically
As you type, MS Excel will start giving you options to choose from. Select the VLOOKUP function from the options.
5. First Parameter - the value to be found
Now we have to give the first parameter 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 parameter.
6. Second Parameter - The table (Column A + Column B)
The second parameter 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 parameter.
7. Third Parameter - Column Index of the value to be found
Now, the third parameter 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 parameter and put a comma to type the next parameter.
8. Fourth Parameter - True or False
The last parameter 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 parameter in the formula. Close the formula with a closing brace ‘)’ and hit enter.
9. Formula calculates and finds the value
The formula will search the value you are looking for ‘1234abcd1’ and the corresponding value ‘1’ will appear in the cell.
10. Copy formula to the remaining cells
Copy & paste this formula in other cells using “CTRL+C” & “CTRL+V”. Paste formula and hit enter.
11. Formula finds all the values
The formula will find all the values you are searching for in the table array.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2013 Petite Hubpages Fanatic