Lookup funtion: column index number (third parameter)

[KipB]

I was hoping Excel 2007 would have a simpler way of identifying the "column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize column
headings as the "column index number" and allow you to pick these from a list
once the table name is selected.

The way to work around this is to include a row of numbers above the lookup
table that counts the columns. This could be hard data or better yet the
formula: =column(X)-column($A)+1. Using the formula updates the column
number if the lookup table adds or deletes columns. In the lookup function
for "column index number" you can enter the cell reference for the desired
column by pointing, or better still give the reference a name ("Price") and
use the name as the reference. Now you have a meaningful lookup function
such as: =vlookup(A6,Data,Price,false).

 

[Dave F]

Here's what I do: I insert a row at the top of the table (assume table goes
from column A to column Z) and enter 1 in A1. Then =A1+1 in B1 and fill to
the right as far as the table goes.

Ergo, when I find the relevant column for the column lookup, all I have to
do is reference the number in the first row of the spreadsheet. Seems far
easier than relying on Microsoft to make it easier.

Dave

 

[T. Valko]

If your columns have descriptive headers then you can use the MATCH function
to find it for you:

.........A......B.......C.......D.......E
1.............Joe....Biff.....Sue....Tom
2...Mon...10......22......30......15
3...Tue......0.......40......4.......19
4...Wed....0.......72......6.......20

Lookup: Tue, Biff

A10 = Tue
B10 = Biff

=VLOOKUP(A10,A2:E4,MATCH(B10,A1:E1,0),0)

Biff

 

[Dave F]

That's very clever.

 

[Roger Govier]

Hi

Use Index / Match instead
If your table has labels in A2:A10 and labels in B1:G1

=IDEX($A$1:$G$10,MATCH(Row_Label,$A$1:$A$10,0),MATCH(Column_label,$B$1:$G$1,0))

 

[Gord Dibben]

Instead of counting across...................

To return the column number from a letter use this Function

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("IV") returns 256


Gord Dibben MS Excel MVP

 

[Harlan Grove]

Instead of counting across...................

To return the column number from a letter use this Function

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("IV") returns 256

....

Someone's gotta ask . . . why is this preferred to just

=COLUMN(IV:IV)

?

Still, it's a pity Microsoft still hasn't figured out how to implement
a work-alike for 123's @XINDEX function, which given Biff's setup,
would return the desired result with the formula
@XINDEX(A2:E4,B10,A10) (note 123's column then row ordering of index
arguments).

 

[jasonc]

Instead of using =vlookup or =hlookup, use =lookup. That way you don't have
to count columns

 

[Roger Govier]

Hi Jason

Yes, you're correct that you don't have to specify column, but it will
only return the value from the last column of the array, and the array
would have to be sorted.

It wouldn't work in this particular scenario.