Hi Neework,
The syntax for VLOOKUP can be found in the Function Browser (Click the ƒx button in the toolbar, and choose Show Function Browser). Here's what it says:
VLOOKUP
The VLOOKUP function returns a value from a range of columns by using the left column of values to pick a row and a column number to pick a column in that row.
VLOOKUP(search-for, columns-range, return-column, close-match)
- search-for: The value to find. search-value can contain any value type.
- columns-range: A range of cells. range is a reference to a single range of cells, which may contain values of any type.
- return-column: A number that specifies the relative column number of the cell from which to return the value. return-column is a number value. The leftmost column in the range is column 1.
- close-match: An optional value that determines whether an exact match is required.
- close match (TRUE, 1, or omitted): If there’s no exact match, select the column with the largest top-row value that is less than the search value. Wildcards can’t be used in search-for.
- exact match (FALSE or 0): If there’s no exact match, return an error. Wildcards can be used in search-for.
Your first formula is returning the result you asked for (although not the resut you wanted):
Translated into English, the formula says:
Search for the value contained in cell C2 (of this table),
Search in the first column of the table consisting of cells A2 to F25 of Table 3,
Return the value in from the column indicated by the value in the cell in this row of the column labeled "H-Power_Inp_Rated,
Argument 4 has been omitted, so a "Close Match" with C2 is acceptable
The 3rd argument, shown in italics in the translation, is the one giving you trouble.
You have used a cell reference here, stating only the column containing the cell. Since the argument requires a single value, Numbers automatically interprets the short reference as a reference to the cell in the same row of that column as occupied by the formula (row 2 in this case), and uses the value contained by that cell as the 3rd argument.
The cell referenced is Cell E2 on Table 3, which contains the value 1.05. The argument requires an integer value greater than zero, so 1.05 is truncated to 1, and the formula returns the value from column 1 of the lookup table; the model number for the unit.
Revising the formula:
For Cell G2 (the position of the formula shown in your first example), you want the value from column E of the lokup table (Table 3). That's the fifth column of the table (A is 1), so the value of the 3rd argument must be 5:
G2: =VLOOKUP(C2, Table 3::A2:F25, 5)
Specifying the value directly means you'll have to change that value (manually) for each column. A better way is to calculate the value, using the COLUMN() function. Column G, where this occurrence of the formula is located, is column 7. Subtract 2 from that, and the result is 5, the value we want for this argument. New version of the formula:
G2: =VLOOKUP($C2, Table 3::A2:F25, COLUMN()-2)
The nice thing about this version is that because you want the data pulled in in the same column order as it is listed on the lookup table, you can place this formula into any cell in row 2, and it will return the correct result.
Note the $ operator added before the C in C2. This keeps that cell reference fixed on column C as the formula is filled right or left.
Enter the formula in D2, then fill it right to H2. With all five cells selected, fill down for as many rows as you need.
Regards,
Barry
PS: Apple provides two excellent resources with Numbers, the Numbers '09 User Guide and the iWork Formulas and Functions User Guide. Chapters 1 to 4 of the numbers guide are highly recommended as 'required reading' for anyone new to Numbers. The rest of that guide, and the F&F guide should be treated as handy references to be consulted when the need arises.
Both are searchable PDF documents, and both are available to download through the Help menu in Numbers.
B
