Take a look at some examples and learn how to use VLOOKUP for your own Excel projects.
What Is VLOOKUP in Excel?
Excel’s VLOOKUP function is similar to a telephone book.
Kaspars Grinvalds /Shutterstock
The V in VLOOKUP stands for vertical, meaning that it searches a column of data.
VLOOKUP only returns data from columns to the right of the lookup value.
Despite being limited to the vertical orientation, VLOOKUP is an essential tool that makes other Excel tasks easier.
VLOOKUP can come in handy when you’recalculating your GPAor evencomparing two columns.
VLOOKUP searches the first column oftable_arrayforlookup_value.
You may choose to restructure your table to meet this requirement.
Then opt for cells to add them as arguments in the formula.
Let’s practice on the sample spreadsheet above.
Assume you have a spreadsheet containing the following columns: Name of products, Reviews, and Price.
You then want Excel to return the number of reviews for a particular product,Pizzain this example.
you might easily achieve this with the VLOOKUP function.
Just remember what each argument will be.
Thetable_arrayis the range containing the table, which isA2:C9.
You should only include the data itself, without the headers in the first row.
Finally, thecol_index_nois the column in the table where the reviews are, which is the second column.
With these out of the way, let’s get to writing that formula.
Excel will now return the reviews for Pizza in cell F6.
This can come in handy when you better perform operations likeplotting Excel graphs or chartson the resulting data.
The trick is to useabsolute cell referencesso they don’t change during autofill.
However, the approximate match can be too lenient, particularly if your list is not sorted.
you might get the price for these products from the parent spreadsheet with VLOOKUP.
The difference here is that you’ll have to go to the parent sheet to opt for table array.
Here’s how:
you could nowsort your Excel datawithout it affecting the original table.
VLOOKUP is a great way to query data faster in Microsoft Excel.
Excel’s XLOOKUP function is similar to VLOOKUPbut can look both vertically and horizontally in a spreadsheet.
Most of Excel’s lookup features follow the same process, with only a few differences.
