Excel VLOOKUP manually

Possibly the most advanced function in the CAT curriculum, Excel’s VLOOKUP (and HLOOKUP for that matter) is something that can easily be mastered with practice.

Imagine the following scenario: you are sitting in your CAT Lab with a list of all the ICT devices in the room. Each row on the list represent a device and has columns for a Serial Number, Device Name, Device Type and Device Description.

Excel Data

As a learner calls out the Serial number of their PC, you must reply with the Device Name. Are you up for the task? Of course you are!

As a Serial Number is called out, you search the first column for that Serial number. When you find a match for that Serial number, you look in that row for the value in the Device Name column and call it out when you find it. Ready?

Me: CGWM9WHK510051428
You: …

Process followed manually
  1. You cast your eye down the Serial Number column. Why? Because I gave you a serial number.
  2. You locate the value called out: CGWM9WHK510051428 in row 12 (would have been easier if the list had been in order, wouldn’t it!)
  3. You cast your eye down the Device Name column (you were tasked with shouting out the device name)
  4. You find the Device Name for row 12 (you identified the correct row — or record — in step 2 above): Mecer M9WHK5 and you shout (return) it out to me

Now we will have Excel perform the task for us using the VLOOKUP function. The VLOOKUP function has 4 arguments:

  1. what to lookup (the Serial Number)
  2. where to look it up (the table, or array of data)
  3. what to return (the Device Name)
  4. must an exact match be found (yes)

Confused already? Don’t worry: Excel explains each argument in detail in the Insert Function dialogue window.

Excel’s VLOOKUP function arguments
  1. We are going to enter the Serial Number we are searching for in cell F2.
  2. We are going to enter the VLOOKUP function in cell G2 and this is where the Device Name is going to be displayed as the result of the function. Type “=VL” in G2 and you will see the VLOOKUP function appear in the auto-complete list — double-click on the VLOOKUP function, and then click on the Insert Function button.
  3. Your cursor should now be in the Lookup_value argument field.
Excel’s VLOOKUP function arguments complete

Lookup_value: this argument is what it says it is — which value are you looking up. We are going to be looking up the value entered in F2, click on cell F2 to enter it in the first argument.

Table_array: where are we going to look this value up? In the big array (set of rows and columns of cells containing data) from A1 to D19. Notice that I made the array an absolute reference. This is strictly not required for this example, but it is not incorrect and it is good practice.

Col_index_num: from which column in the Table_array (the range you selected in the previous argument) do you want the value returned? We want the value from the Device Name column. That is the second column in the Table_array, so we enter a 2 (we start counting at one and move left-to-right the same as we always do when we read).

Range_lookup: must the function find an exact match or the closest match. For this example, we want the function to search for the exact Serial Number.

COMING SOON: part 2 using the closest match option!

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.