If you have a large spreadsheet containing a lot of information, you may find it hard to find specific information on that spreadsheet. However, software like Excel, LibreOffice Calc and Google Spreadsheet provide tools you can use to locate the information you need quickly. Use the VLOOKUP function to help you search for and match data you have in one section of your spreadsheet to the contents of a cell or range of cells in another.

Select the first cell in the column you want to display VLOOKUP's results in. For example, if you have a table in which column "H" contains color names and column "I" contains their respective hexadecimal codes, but cells A2 through A7 are separated from the table and contain color names that you want to match with their hex value, select cell B2 to add the VLOOKUP function to.

Type the following formula into the selected cell, but don't press the "Enter" key or click the "Enter" formula button:

\=VLOOKUP(

Click the first cell containing the information you want to match. For example, click cell A2 that contains the first color you want to find the hex number for.

Type a comma after the cell's reference in the VLOOKUP formula, so that your formula now resembles the code below:

\=VLOOKUP(A2,

Select all the cells within the reference table you will search for matching information in, and then type a comma after cell range. For example, if your table contains nine different colors and their matching hex values, select cells H2 through I10:

\=VLOOKUP(A2,H2:I10,

Type the table column number that contains the data you want to display when there is a match, and then add a comma. Excel uses the leftmost column as the index and labels it as column “1.” For instance, type "2" for the column containing the hex values in the table:

\=VLOOKUP(A2,H2:I10,2,

Type "False)" (omit the quotes) into the formula, and then press "Enter." This forces VLOOKUP to display only exact matches. If you type "True" instead, VLOOKUP will display approximate matches -- for instance, both "black" and "Blakk" will match with the color “Black” in a reference table.

Select the cell with your completed VLOOKUP formula, press “Ctrl-C,” select the other cells in that column that you want to apply the formula to, and then press “Ctrl-V.” Your spreadsheet application pastes the formula into the other cells and adjusts the cell references automatically for you.

Tip

Check the data in the columns you want to match and in your reference table to make sure that they have the correct data format and that text cells contain no extra spaces. For example, if the color "Black" is in the reference table, but the cell you want to match contains " Black", VLOOKUP displays "#NA," meaning "not available."

Warning

Information in this article applies to Excel 2013, LibreOffice Calc 4.0 and Google Spreadsheet. It may vary slightly or significantly with other versions of these applications.