Even though you might have understood how to use vlookup it is essential that you get some idea on where it could be used. The real world scenarios and examples are the best way to ensure that one has completely got hold of the concept.
Below are some of the real world examples and scenarios where you could apply vlookup.
Compare Two Columns
This is a simple but most common requirement that you would face every day, compare two columns and see if values in one column are available in the another column.
To map it with a real world example, let’s say you have organized a school trip and you have the list of students who have voted yes to join. Now you may need to get the list of students who haven’t voted yes and see if you could possibly convince them to go on trip with you. Let us quickly see how to do this using vlookup.
You may download this sample sheet that holds the base data which we are about to discuss.
We have two sheets, “Student Names” and “Students Voted Yes”. In Student Names list we have the base list of all students in a class. The list of names is available in column J.
In “Students Voted Yes” sheet we have the list of students who have voted yes for the trip, in column A.
So let’s go to the column K in “Student Names” sheet and enter the below formula,
=NOT(ISNA(VLOOKUP(J3,’Students Voted Yes’!A:A,1,FALSE)))
Let us now dissect and understand what the above formula means,
- Hope you might not have any issues understanding the vlookup formula VLOOKUP (J3,’Students Voted Yes’! A: A, 1, FALSE), it just looks for the value in J3 cell and searches for it in the column A of “Students Voted Yes” sheet.
- The ISNA is wrapped around the Vlookup function to check, if the vlookup we are trying to perform returns a #N/A. If you do a plain Vlookup and if you don’t get a match for the value you are trying to find, then you will get a #N/A. So IsNA function will help us to avoid seeing that odd value.
- NOT is wrapped around the entire function to get the actual result that we need.
I have provided this step by step progress in the sheet that you have downloaded. The “Student Names” sheet column K shows the result of plain vlookup, column L shows what you get if you apply IsNA function and the column M shows the desired result that we require.
Do share your real world scenarios where you have used vlookup in the below comments so that it would be useful for someone else.