1. ## VLOOKUP (Any)

OK - this is a history of Excel question!!

Can anyone tell me WHY when you specify the COL INDEX NUMBER value with the VLOOKUP function you use the coloumn number in the table rather than just the column letter?

Thanks, Laurie

2. ## Re: VLOOKUP (Any)

If the lookup table starts in column A, the column letter for the column in which to look corresponds to the actual column header: the third column is C, etc.

But if the lookup table starts, say, in column D, and you want to retrieve a value from the third column of the table, which letter do you want to use? C because it is the third column in the table, or F because that is the "physical" column? The first may lead to confusion, the latter makes it difficult to move the lookup table to another location in the worksheet.

3. ## Re: VLOOKUP (Any)

I would suspect that it is because it makes it easier to used a value in a cell to decide which column in the table to use. It would be much harder to do a two dimensional lookup if you had to calculate a cell address in the cell specifying the column.

4. ## Re: VLOOKUP (Any)

Yes Ithis is basically what I had thought.

It just doesn't seem to be consistent with other features of Excel. And from a user point of view it would be much easier to be able to just select the column, especially if the table has lots of columns (as ours do).

Maybe it's just the way Microsoft built it!

5. ## Re: VLOOKUP (Any)

But if the column is determined by some data, just selecting the column is very difficult if you need to calculate an A1 style reference. If this is a big problem, then you can use something like this (assuming your table is in A1100 and you want to return the value in column D):

<pre>=VLOOKUP(E1,\$A\$1:\$D\$100,COLUMN(D1)-COLUMN(A1)+1,FALSE)
</pre>

6. ## Re: VLOOKUP (Any)

This is one reason I prefer to use the INDEX MATCH technique for anything but the simplest of lookups. I really don't like VLOOKUP because if you add a column to the middle of your table, you could end up with the wrong answer.

=INDEX(AC1:AC100,MATCH(A1,Q1:Q100))
is equivalent to
=VLOOKUP(A1,Q1:AC100,n) [I'm too lazy to calculate n]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•