Back

Lookup & Reference Formulas

• You have a table of data containing: part number, cost, and selling price. How can you enter a part number and automatically pull out the cost (or selling price)?

  The Vlookup function will look for a value in the leftmost column of a table and then returns a value from that row as determined by the user. The syntax is "=VLOOKUP(VALUE TO LOOK UP,DATA TABLE ADDRESS,NUMBER OF COLUMNS OVER FROM THE VALUE FOUND,FIND AN EXACT MATCH)".

  To continue the example, you have a data table residing in a worksheet named "Data" and the table is in cells A1 to C15. The table consists of "ID Number" in column A, "Cost" in column B, and "Selling Price" in column C. In your current worksheet, you want to input the "ID Number" in cell A1 and have cell A2 return the "Cost" as specified in your data table. The formula you would need in cell A2 would be:

  =vlookup(A1,Data!A1:C15,2,FALSE).

  The first argument, "A1", tells Excel what it needs to look for. The next argument, "Data!A1:C15", tells Excel where to look for it. Note that the value you are searching for must be in the first column. The next argument, "2", tells Excel how many columns over to return the value (the first column being the id number). The final argument, "FALSE", tells Excel that you want to find an exact match.

TOP
• The Hlookup Function is similar to the Vlookup function except it looks for a value in the top row of a table of data and then returns a value from that column as determined by the user. The syntax is "=HLOOKUP(VALUE TO LOOK UP,DATA TABLE ADDRESS,NUMBER OF ROWS DOWN FROM THE VALUE FOUND,FIND AN EXACT MATCH)". Note that the value you are searching for must be in the first row of data.

TOP
• There have been occasions when I have had a range of data that I wanted to transpose. That is, I wanted each column to be a row and each row to be a column, sort of rotating the data. Excel's Transpose Function will do this for me. This function is different from a normal formula because you must "array" enter it. This means you press CTRL+SHIFT+ENTER, instead of just pressing ENTER. Also, you must have the same number of rows and columns as your data has columns and rows. An example will illustrate this best.

  For example, I have a data table (A1:M17) on Sheet1 set up as follows - B1:M1 contains the months (January to December), A2:A17 contains names, B2:M17 contains my sales data. I want this information on Sheet2, but I want the months running down column A and the names running across row 1 (the reverse of the set up on Sheet1). Here's what you do. On Sheet2, highlight cells A1:Q13. This highlights 13 rows and 17 columns. Note that your original data (in Sheet1) has 13 columns and 17 rows. Keep this area highlighted and type =TRANSPOSE(Sheet1!A1:M17), but don't press ENTER. Instead, press CTRL+SHIFT+ENTER. Now, the last thing left is to format cell A1. Since A1 on Sheet1 is blank, I am getting a zero in A1 on Sheet2. Now I am a bit of a stickler for detail and I don't want this zero being displayed (doesn't look nice). However, I can't just delete the one formula because it is part of an array formula. So I custom formatted this one cell to display nothing for zero. I right-clicked on cell A1, selected Format Cells, selected Custom in the Number tab, and typed ;;"" (two semi colons and two quotation marks).

TOP
• This question was posed to me: I have a table where I want to find a value in the header (first row), look for a value under that column, and then return the value in the first column of the table. I can't use VLOOKUP because the values I am using are variables that I want to input.
  
  Now this one's really, really hard to explain without a picture. Click on the hyperlink to see a better explanation of the problem along with the solution.

TOP

Copyright ^© 2001 by Barrie R. Davidson
Last updated April, 2002