Lookup functions are used to lookup specific values from elsewhere in your workbook and return information from corresponding rows and columns.
Lookup functions are used to lookup specific values from elsewhere in your worksheet – or from a different worksheet altogether – and return information from corresponding rows and columns.
Lookup functions can be especially useful when searching for values in worksheets with hundreds or thousands of rows and columns.
How to Use Lookup Functions
In the examples below, we’ll take a look at some commonly used lookup functions. For documentation on every lookup function supported by Spreadsheet.com, reference our section on Lookup functions.
When using functions in your workbook, you can input and edit formulas either directly in the cell, or from the formula bar between the worksheet toolbar and the spreadsheet grid. Learn more in our article on Creating and Editing Formulas.
How to Perform a Vertical Lookup (VLOOKUP)
The VLOOKUP function is used to search for a value within a range. VLOOKUP looks for the specified value in the first column of the range and returns a value from a different column in the matching row.
In the example above, we’ve input a VLOOKUP function in Cell F6 that searches the data table on the left for the value in Cell E6 and then returns the corresponding value from Column C. Let’s take a look at our VLOOKUP function more closely and break it down step by step:
=VLOOKUP(E6,A2:C6,3)
- The function looks at the value in Cell E6.
- The function looks for that value in the first column of the range A2:C6.
- From the row where the function identifies that value, it returns the corresponding value from the third column in the specified range.
If we change the lookup value in Cell E6, the VLOOKUP function output will update accordingly.
In the example above, we’ve changed the value in Cell E6 from “Account 2” to “Account 3”. The VLOOKUP formula hasn’t changed, but the output is now returning the value from Column C in the Account 3 row.
Alternatively, we can change the index number in the VLOOKUP function’s third argument to return a value from a different column.
In this example, we’ve changed the index from “3” to “2” so that the VLOOKUP function returns a value from the second column in our range, the Region column.
VLOOKUP functions have many uses, and can be especially useful when searching for values in worksheets with hundreds or thousands of rows and columns. Like other functions, they can even be used to reference data from other worksheets.
If VLOOKUP doesn’t work well for your data and your needs, take a look at HLOOKUP and XLOOKUP. These are similar lookup formulas, but search through data in different ways.
How to Create a Hyperlink with Custom Text (HYPERLINK)
When you paste a URL into a cell, Spreadsheet.com will automatically convert the cell to the URL data type and automatically convert the link text to a clickable hyperlink. If you want your hyperlink to read as custom text instead of the plain URL, you can use the HYPERLINK function.
The HYPERLINK function takes two arguments – a URL and the text that will appear in place of the URL.
When you click on the cell with the HYPERLINK function, a popup will appear above where you can click and visit the URL included in your formula.
More Lookup Functions
These are just some of the many lookup functions supported by Spreadsheet.com. Take a look at our full suite of articles on lookup functions to learn more.
Like lookup functions, Related rows can help pull in data from elsewhere in your workbook. Or, read on to learn more about other types of functions supported by Spreadsheet.com, including financial functions, logical functions, statistical functions, and more.