ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Tutorial – MS Excel – How to use VLookup in Microsoft excel

Updated on October 4, 2016

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)

  1. Lookup Value = the cell number which is to be searched in the table.
  2. Table Array = The table which has all the values
  3. Column Index for lookup = the index of the column in the table array which is to be searched
  4. Exact or approximate match = this argument should be TRUE for an approximate match & FALSE for an exact match.

Steps:

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.

Special Condition:

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 entrie 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)

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article