| VLOOKUP Function |
|
|
|
|
| VLOOKUP is an Excel function to lookup and retrieve data from a
specific column in table. VLOOKUP supports approximate and exact matching,
and wildcards (* ?) for partial matches. The "V" stands for
"vertical". Lookup values must appear in the first column of the
table, with lookup columns to the right. |
|
|
|
|
|
|
| FORMULA |
|
|
|
|
|
| =VLOOKUP(lookup_value,table_array,
col_index_num, [range_lookup]) |
|
|
|
|
|
|
|
| VALUE - The value to look for in the
first column of a table. |
|
|
| TABLE - The table from which to
retrieve a value. |
|
|
| COL_INDEX_NUM - The column in the
table from which to retrieve a value. |
|
| RANGE_LOOKUP - [optional] TRUE =
approximate match (default). FALSE = exact match. |
|
|
|
|
|
|
| SEARCH |
|
|
|
|
|
| ID Number |
First Name |
Last Name |
Email Address |
Department |
|
| 567 |
Holly |
Holm |
hh@hh.com |
OPD |
|
|
|
|
|
|
|
| 1 |
2 |
3 |
4 |
5 |
|
| ID Num |
First Name |
Last name |
Email Address |
Department |
|
| 123 |
Joe |
Rogan |
jr@gmail.com |
HR |
|
| 234 |
Brendan |
Schaub |
br@aol.com |
Purchasing |
|
| 345 |
Ronda |
Rousey |
rr@yahoo.com |
Med Admin |
|
| 456 |
Manny |
Pacquiao |
mp@hotmail.com |
OR |
|
| 567 |
Holly |
Holm |
hh@hh.com |
OPD |
|
|
|
|
|
|
|
| MATCH
FUNCTION |
|
|
|
|
| The
basic MATCH function returns a NUMBER based on the relative position of a
lookup value within a defined array / column. |
|
|
|
|
|
|
|
| FORMULA |
|
|
|
|
|
| =MATCH(lookup
value, lookup array, match type) |
|
|
|
|
|
|
|
|
| LookUpValue |
RESULT |
|
|
|
|
| Pacquiao |
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| INDEX
FUNCTION |
|
|
|
|
| =INDEX(array,MATCH
formula ) |
|
|
|
|
|
|
|
|
|
| LookUp Value |
Index Match |
|
|
|
|
| Last Name |
ID Num |
|
|
|
|
| Pacquiao |
456 |
|
|
|
|
| Rousey |
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
| =INDEX
( Column I want a return value from , MATCH ( My Lookup Value , Column I want
to Lookup against , Enter “0” )) |
|
|
|
|
|
|
| VLOOKUP
to a different Sheet |
|
|
|
|
| Formula |
|
|
|
|
|
| =VLOOKUP(A2,Sheet1!F10:G20,2,FALSE) |
|
|
|
