I use the Vlookup a lot and sometimes in some very large spreadsheets.
As you can imagine I spend a lot of time counting columns to find the
column index number. Is there a formula I can use that will do that
counting for me? For example if my lookup value is in column A and my
return value is in column C the answer for this formula should be 3.

Thank you,
Shelly


--
shelly2
------------------------------------------------------------------------
shelly2's Profile: http://www.excelforum.com/member.php...o&userid=29852
View this thread: http://www.excelforum.com/showthread...hreadid=495522

"shelly2" wrote in
message ...

I use the Vlookup a lot and sometimes in some very large spreadsheets.
As you can imagine I spend a lot of time counting columns to find the
column index number. Is there a formula I can use that will do that
counting for me? For example if my lookup value is in column A and my
return value is in column C the answer for this formula should be 3.

Tools/ Options/ General/ R1C1 reference style
will give the columns numbers, rather than letters.
--
David Biddulph

But it's only 3 as long as the datalist starts in Column A.
If it starts in Column B, the C becomes 2!

How would you want to resolve that?

If you're always starting in Column A, and you enjoy typing, use the column
label itself instead of the column index number, like:

=VLOOKUP(E1,A1:D17,COLUMN(C:C),0)
OR
=VLOOKUP(E1,A1:D17,COLUMN(C1),0)

Which return the exact same results as:

=VLOOKUP(E1,A1:D17,3,0)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"shelly2" wrote in
message ...

I use the Vlookup a lot and sometimes in some very large spreadsheets.
As you can imagine I spend a lot of time counting columns to find the
column index number. Is there a formula I can use that will do that
counting for me? For example if my lookup value is in column A and my
return value is in column C the answer for this formula should be 3.

Thank you,
Shelly


--
shelly2
------------------------------------------------------------------------
shelly2's Profile:
http://www.excelforum.com/member.php...o&userid=29852
View this thread: http://www.excelforum.com/showthread...hreadid=495522

Thank you David. That might be the best solution.

My lookup value isn't always column A. What I was hoping for was
something like =column AB-column D. Now I know that won't work but is
there something that will work?

Thanks again,
Shelly


--
shelly2
------------------------------------------------------------------------
shelly2's Profile: http://www.excelforum.com/member.php...o&userid=29852
View this thread: http://www.excelforum.com/showthread...hreadid=495522

Shelly

Copy/paste this UDF to a General module in your workbook.

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

Then your vlookup formula as such.........

=VLOOKUP(cellref,table,GetColNum("e"),FALSE)


Gord Dibben Excel MVP


On Thu, 22 Dec 2005 11:18:41 -0600, shelly2
wrote:


Thank you David. That might be the best solution.

My lookup value isn't always column A. What I was hoping for was
something like =column AB-column D. Now I know that won't work but is
there something that will work?

Thanks again,
Shelly

Shelly

=VLOOKUP(cellref,table,Column(E:E),FALSE) should also work.

No need for the UDF


Gord

On Thu, 22 Dec 2005 12:27:12 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Shelly

Copy/paste this UDF to a General module in your workbook.

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

Then your vlookup formula as such.........

=VLOOKUP(cellref,table,GetColNum("e"),FALSE)


Gord Dibben Excel MVP


On Thu, 22 Dec 2005 11:18:41 -0600, shelly2
wrote:


Thank you David. That might be the best solution.

My lookup value isn't always column A. What I was hoping for was
something like =column AB-column D. Now I know that won't work but is
there something that will work?

Thanks again,
Shelly