VLOOKUP(), the Vertical Lookup function, is very useful to sort through a large table of records and find values associated with any column to the RIGHT of your reference column.
For example, in a table of ID NUMBERS and AGE, you can type in an ID NUMBER and find the age of that person easily.
GENERAL SYNTAX is:
VLOOKUP (lookup_value, table_array,column_number,TRUE_or_FALSE)
lookup_value parameter defines the FIRST VALUE (the row) you are searching.
table_array parameter defines the range of the table.
column_number parameter defines the SECOND VALUE (the column) you are searching. The value of the cell where this column intersects with the above row will be output as the RESULT of the search.
TRUE_or_FALSE Boolean (logical) value defines whether you want an exact match (FALSE) or approximate match (TRUE).
Here is an example of finding STUDENT PROFILES:
NOTE 1: All cell references are ABSOLUTE CELL REFERENCES, with $ signs in front of cell addresses.
This example retrieves four variables for every NAME entered into the cell B1.
Every time you change the value of B1, cells B3, B4, B5 and B6 also change automatically! Isn’t that great?
The formula returns the values corresponding only to the first Bob on the lost and ignores the second Bob. Thus make sure there are NO MATCHING VALUES in your search column.
NOTE 3: If you enter a NAME that is not in the table, you’ll get error messages:
For example, in a table of ID NUMBERS and AGE, you can type in an ID NUMBER and find the age of that person easily.
GENERAL SYNTAX is:
VLOOKUP (lookup_value, table_array,column_number,TRUE_or_FALSE)
lookup_value parameter defines the FIRST VALUE (the row) you are searching.
table_array parameter defines the range of the table.
column_number parameter defines the SECOND VALUE (the column) you are searching. The value of the cell where this column intersects with the above row will be output as the RESULT of the search.
TRUE_or_FALSE Boolean (logical) value defines whether you want an exact match (FALSE) or approximate match (TRUE).
Here is an example of finding STUDENT PROFILES:
NOTE 1: All cell references are ABSOLUTE CELL REFERENCES, with $ signs in front of cell addresses.
This example retrieves four variables for every NAME entered into the cell B1.
Every time you change the value of B1, cells B3, B4, B5 and B6 also change automatically! Isn’t that great?
Here is the FORMULA BREAKDOWN for cell B3:
=VLOOKUP($B$1,$D$2:$H$8,2,FALSE)
- $B$1 — the formula will always first look at the value entered and saved into cell B1.
- $D$2:$H$8 – the formula will then search the whole range of the table which occupies cells D2:H8.
- 2 – the formula will go to the SECOND (from left) column of the table for the ROW that corresponds to the B1 value (“Cruz”), read it, and display it as the RESULT that you see (“Biology”).
- FALSE – the formula will return results only if the value in B1 is an exact match. If not, it’ll return an error message.
Formula for cell B3 refers to column 2 since MAJOR is the second column of the table.
Formula for cell B6 refers to column 5 since GPA is the fifth column of the table.
NOTE 2: If there are more than one exact search value, VLOOKUP returns only the first match. For example, see what happens when we have more than one Bob in the list:
The formula returns the values corresponding only to the first Bob on the lost and ignores the second Bob. Thus make sure there are NO MATCHING VALUES in your search column.
NOTE 3: If you enter a NAME that is not in the table, you’ll get error messages:
A process flow for Vlookup:
No comments:
Post a Comment