# Examples of VLOOKUP Function in Excel: 7 Ideal Examples

### Method 1 – Search for Particular Value

Steps:

• Look at the following image, where you want to find Paul’s marks on his G. Science exam.

• Insert the following formula in cell D12.
`=VLOOKUP(D11,\$B\$4:\$F\$9,4,0)`
• The formula will search for the name of cell D11 in the cell range of B4:F9. After finding the match, it will show the value from the fourth column of the data range that contains the marks of the G. Science of all the students.

• After pressing Enter, it will show the marks of Paul from that particular column.

• If you input any name in cell D11 from the value of cell range B5:B9, it will show the marks of that person.

### Method 2 – Perform Dynamic Search

#### Example 2.1: Execute Two-Way Lookup

Steps:

• Make three extra fields for the name of the person, the subject, and the marks.
• In cell D13, use the following formula.
`=VLOOKUP(D11,\$B\$5:\$F\$9,MATCH(D12,\$B\$4:\$F\$4,0),0)`
• After pressing Enter, it will show the marks for the G. Science exam in May.
• If you change any value in cell D11 or D12, the value of cell D13 will change accordingly.

Formula Breakdown

=VLOOKUP(D11,\$B\$5:\$F\$9,MATCH(D12,\$B\$4:\$F\$4,0),0)

• The MATCH function will look for the cell value in column range B4:F4 and return its relative position.
• The VLOOKUP function will look in that particular column for the specific person’s score in cell D11.

#### Example 2.2: Employ Three-Way Lookup

Steps:

• Take a sample like the following data set where I have some students’ marks for two-term exams.
• Find out the marks of each student based on three different criteria.
• In cell D23, write the following formula.
`=VLOOKUP(D21,CHOOSE(IF(D20="First Term Exam",1,IF(D20="Final Exam",2,3)),B6:F10,B14:F18),MATCH(D22,B5:F5,0),0)`
• See the value of the marks after pressing Enter.
• The formula’s result will change if you change any value of cells D20, D21, or D22, as in the following image.

Formula Breakdown

=VLOOKUP(D21,CHOOSE(IF(D20=”First Term Exam”,1,IF(D20=”Final Exam”,2,3)),B6:F10,B14:F18),MATCH(D22,B5:F5,0),0)

• The MATCH function will look for the subject name.
• The IF function checks the type of exam from cell D20 and will return the range of marks depending on that exam and the subject name, and the CHOOSE function selects the right range of values.
• The VLOOKUP function will match the cell value of D21 and return the marks of the respective subjects of that respective term exam.

### Method 3 – Find Final Cell Value

Steps:

• Find out the last cell value that is a text, and type the following formula in cell D11.
`=VLOOKUP("zzz",B5:B9,TRUE)`
• The “zzz” string in the formula is the largest of any names or texts you can write with alphabets, so inserting this in the formula will always show the last text from a given cell range.

• After pressing Enter, you will get your desired name.

• To get the last cell value of a range that contains a number, use the following formula in cell D12.
`=VLOOKUP(9.99999999999999E+307,C5:C9,TRUE`)
• The term or value ” 9.99999999999E+307” is considered the biggest numerical value in Excel. Using this term in the formula will get the last value of a cell range that is a number.

• Hit Enter to get the result of the above formula.

### Method 4 – Cary Out Partial Lookup

Steps:

• Look at the following image where the primary data set contains the full name, but the secondary does not.
• Use the following formula to get an exact match of the names in the secondary data set with the primary ones in cell C12.
`=VLOOKUP("*"&B12&"*",B5:B9,1,FALSE)`
• Using the asterisk sign before and after B12, the formula will look only for the exact match of the cell value B12 in the cell range B5:B9 and show the result based on that match.

• Use Enter and then drag the formula using AutoFill to get all the desired results.
• Any name that does not match the values of the given cell range will get #N/A as a result.

### Method 5 – Handle Errors with VLOOKUP Function

Steps:

• You have a name in cell D11 that doesn’t match the values of your lookup range.
• To show the result of the name as not found in cell D12, use the following formula.
`=IFERROR(VLOOKUP(C11,\$B\$5:\$B\$9,2,0),"Not Found")`
• The IFERROR function will check if the result of the VLOOKUP function is true.
• If it finds the result false, it will show Not Found.

• Press Enter to get the result, which is negative for this case.

### Method 6 – Execute Case-Sensitive Lookup Formula

Steps:

• In the following image, you can see the data set with three types of names.
• These names are the same but in different cases, and their respective marks are also different.

• Find the marks for the individual by considering the cases in their names; insert the following formula for the first name in cell C16.
`=VLOOKUP(MAX(EXACT(B16,\$B\$5:\$B\$13)*(ROW(\$B\$5:\$B\$13))),CHOOSE({1,2},ROW(\$B\$5:\$B\$13),\$C\$5:\$C\$13),2,0)`

Formula Breakdown

=VLOOKUP(MAX(EXACT(B16,\$B\$5:\$B\$13)*(ROW(\$B\$5:\$B\$13))),CHOOSE({1,2},ROW(\$B\$5:\$B\$13),\$C\$5:\$C\$13),2,0)

• The EXACT function will look for an exact match of the cell value of B16 in the cell range B5:B13.
• The result from the previous step will be multiplied by the ROW function, and the row number will be returned for the exact match of the previous step in an array.
• The VLOOKUP function will match the cell value of D21 and return the marks of the respective subjects of that respective term exam.
• After pressing Enter, the above formula will show the result for that particular name.
• Use the Fill Handle to drag the formula to the lower cells to get the results of the following names.

### Method 7 – Perform VLOOKUP with Multiple Criteria

Steps:

• You have a data set like the GIF, which shows some students’ names and marks in two-term exams.
• Find the marks of each student in those two exams.
• Type the following formula in cell C17.
`=VLOOKUP(\$B17&"|"&C\$16,CHOOSE({1,2},\$B\$5:\$B\$14&"|"&\$C\$5:\$C\$14,\$D\$5:\$D\$14),2,0)`
• After pressing Enter, you will see Paul’s first term marks in C17.
• Drag the formula to the bottom and then to the right to see all the students’ marks.

Formula Breakdown

=VLOOKUP(\$B17&”|”&C\$16,CHOOSE({1,2},\$B\$5:\$B\$14&”|”&\$C\$5:\$C\$14,\$D\$5:\$D\$14),2,0)

• The CHOOSE function will give the values from each column from B:D of the cell range B5:D14 one by one.
• The VLOOKUP function will match the cell values of B17 and C16 with the data from the previous result and find an exact match to show the final result.

## Things to Remember

• Remember to give the correct lookup range and col index number reference in the formula. Otherwise, you will not get the desired result.
• While searching with the VLOOKUP function formula, be careful about the exact match and case sensitivity. The function is not case-sensitive and will always show the first value that matches the criteria despite not matching the case.
• If you are not using Microsoft Office 365, press  Ctrl + Shift + Enter instead of only Enter to get the result from an array formula.

## Related Articles

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF