For this, enter the lookup ranks in some cells (E2:E4 in the screenshot below), and use the following formulas:Įnter the above formulas in cells F2 and G2, select those cells, and drag the formulas to the below rows: To our sample table, we have added one more column that ranks the animals by speed (column A) and want to find the 1 st, 5 th and 10 th fastest sprinter in the world. To see how it works in practice, please consider the following example. When the formula gets copied down the column, the reference will adjust automatically for each row. The lookup value in most cases should be a relative reference (like E2) or you can lock only the column coordinate ($E2). When using VLOOKUP formulas in real-life worksheets, the main rule of thumb is this: lock table array with absolute cell references (like $A$2:$C$11) to prevent it from changing when copying a formula to other cells. With all the arguments established, you should have no problem reading the whole formula: search for "lion" in A2:A6, find an exact match, and return a value from column B in the same row.įor the sake of convenience, you can type the value of interest in some cell, say E1, replace the "hardcoded" text with the cell reference, and get the formula to look up any value you input in E1:ĭoes anything remain unclear? Then try looking at it this way:
VLOOKUP(lookup_value, table_array, col_index_num, ) The syntax for the VLOOKUP function is as follows:
The function is available in all versions of Excel 365 through Excel 2007. The letter "V" stands for "vertical" and is used to differentiate VLOOKUP from the HLOOKUP function that looks up a value in a row rather than column (H stands for "horizontal"). In its common usage, Excel VLOOKUP searches through your data set based on the unique identifier and brings you a piece of information associated with that unique identifier. More technically, the VLOOKUP function looks up a value in the first column of a given range and returns a value in the same row from another column. What is VLOOKUP? To begin with, it is an Excel function :) What does it do? It searches for the value you specify and returns a matching value from another column. We will also provide formula examples that cover the most typical usages of VLOOKUP in Excel, and try to make them both informative and fun. We will try to explain the basics in a very plain language to make the learning curve for an inexperienced user as easy as possible.
This article begins a series covering VLOOKUP, one of the most useful Excel functions and at the same time one of the most intricate and least understood.
You'll learn how to Vlookup from another sheet and different workbook, search with wildcards, and a lot more. Today we'll look at how to use VLOOKUP in Excel with many detailed step-by-step examples.