vlookup

 

The VLOOKUP function in Excel is used to search for a value in the first column of a table and return a value in the same row from a specified column. Below are 10 examples of how to use the VLOOKUP function, including sample data and formulas.

Example 1: Basic VLOOKUP

Data:

Copy

A       B
1  ID    Name
2  101   Alice
3  102   Bob
4  103   Charlie

Formula:

excel

Copy

=VLOOKUP(102, A2:B4, 2, FALSE)

Result: Bob

Example 2: VLOOKUP with Exact Match

Data:

Copy

A       B
1  ID    Name
2  101   Alice
3  102   Bob
4  103   Charlie

Formula:

excel

Copy

=VLOOKUP(102, A2:B4, 2, FALSE)

Result: Bob

Example 3: VLOOKUP with Approximate Match

Data:

Copy

A       B
1  Score Grade
2  90    A
3  80    B
4  70    C

Formula:

excel

Copy

=VLOOKUP(85, A2:B4, 2, TRUE)

Result: B

Example 4: VLOOKUP with Table Array

Data:

Copy

A       B       C
1  ID    Name    Age
2  101   Alice   23
3  102   Bob     30
4  103   Charlie 25

Formula:

excel

Copy

=VLOOKUP(102, A2:C4, 3, FALSE)

Result: 30

Example 5: VLOOKUP with Different Sheet

Data in Sheet1:

Copy

A       B
1  ID    Name
2  101   Alice
3  102   Bob
4  103   Charlie

Data in Sheet2:

Copy

A
1  ID
2  102

Formula in Sheet2:

excel

Copy

=VLOOKUP(A2, Sheet1!A2:B4, 2, FALSE)

Result: Bob

Example 6: VLOOKUP with Wildcard

Data:

Copy

A       B
1  ID    Name
2  101   Alice
3  102   Bob
4  103   Charlie

Formula:

excel

Copy

=VLOOKUP("10*", A2:B4, 2, FALSE)

Result: Alice

Example 7: VLOOKUP with Multiple Criteria

Data:

Copy

A       B       C
1  ID    Name    Age
2  101   Alice   23
3  102   Bob     30
4  103   Charlie 25

Formula:

excel

Copy

=VLOOKUP(102, A2:C4, 3, FALSE)

Result: 30

Example 8: VLOOKUP with Error Handling

Data:

Copy

A       B
1  ID    Name
2  101   Alice
3  102   Bob
4  103   Charlie

Formula:

excel

Copy

=IFERROR(VLOOKUP(104, A2:B4, 2, FALSE), "Not Found")

Result: Not Found

Example 9: VLOOKUP with Dynamic Column Index

Data:

Copy

A       B       C
1  ID    Name    Age
2  101   Alice   23
3  102   Bob     30
4  103   Charlie 25

Formula:

excel

Copy

=VLOOKUP(102, A2:C4, MATCH("Age", A1:C1, 0), FALSE)

Result: 30

Example 10: VLOOKUP with Array Formula

Data:

Copy

A       B       C
1  ID    Name    Age
2  101   Alice   23
3  102   Bob     30
4  103   Charlie 25

Formula:

excel

Copy

=VLOOKUP({102,103}, A2:C4, 2, FALSE)

Result: Bob, Charlie

These examples demonstrate various ways to use the VLOOKUP function in Excel, including basic lookups, approximate matches, error handling, and more advanced techniques like dynamic column indexing and array formula