Okay, let's look at the syntax for the VLOOKUP function from the help
files...
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The first argument (lookup_value) is the item you want to find. You called
that Cell 1 and I said to assume Cell 1 was A10. Whatever is typed into Cell
1 (A10) is what will be looked up by the function.
The second item (table_array) is the range for the table of values you want
to look into in order to find whatever is in the first argument. For your
given data, that was A1
4.
At this point, there are two things to note. First, the 'lookup_value'
(first argument) will be searched for only in the first column of the range
given for the table in 'table_array' (second argument). Second, the cell
reference in the first argument and the range reference in the second
argument were all given as absolute references (the $ preceding the row and
column designators). Why? Because I knew that after constructing the
formula, it was going to be copied to other cells. Since the location of the
'lookup_value' and the table's location was fixed, I did not want the
references to be incremented when copied. The absolute references guaranteed
that wouldn't happen.
Okay, now, the third argument (col_index) is the number of the column you
want to return your value from. The column is numbered within the table, not
by the actual column on the spreadsheet. By that, I mean if your table were
located at F10:J13, column "F" would be the table's first column, column "G"
its second column, etc. So, the column we want to look up the value in, for
the formula located in A11 (Cell 2), was 2. When we copy the formula across
to Cell 3 and Cell 4, the column numbers from the table for them will be 3
and 4. You are probably wondering why I didn't simply put 2 in for the third
argument (for the formula located in Cell 2). Well, because, when you copied
that formula across to the other cells, the 2 (a constant number) would not
be incremented. I needed something that would increment when copied. So, I
called upon the Column function to do that. Unsurprisingly, the Column
function returns the column number for reference cell given as it argument
(or the first cell of a range reference). So, I used that in order to get a
reference that would increment when copied. Column(B2) returns 2 because the
"B" in B2 is column number 2 in the spreadsheet (not because it also happens
to be the second column in the table). When the formula is copied across,
the B2 reference will become C2 and D2 respectively in next cells copied to
(and the column function will return 3 and 4 for them, thus pulling data
from the next columns in the table). Just one more mention on this... if the
table had been located in, say, F10:J13, then Column(F11) would not have
been sufficient for the column number. Remember, the column number for the
VLOOKUP function is the column's number within the table itself, not the
spreadsheet in general. Column(F11) would return, obviously, 11 and not the
2 we needed. For this situation, we would have had to use COLUMN(F11)-9 for
the third argument in order to feed it the 2 necessary for the second
(table) column lookup.
Finally, for the last argument, I used FALSE, which told the VLOOKUP
function that the first column was not in a sorted order. If we used TRUE or
omitted the argument altogether (the default value for this optional
argument is TRUE), then VLOOKUP would have assumed the values in the table's
first column were in ascending sort order. One more thing about this
argument... if FALSE is used, then the search that takes place is for and
exact match to the 'lookup_value' (first argument) and if TRUE is used, the
search will locate exact or approximate matches to the 'lookup_value'.
I think that covers it all... I hope it helped you some.
Rick
