Vlookup help

[shelly2]

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

[David Biddulph]

"shelly2" <[email protected]> wrote in
message news:[email protected]...
>
> 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

[RagDyeR]

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" <[email protected]> wrote in
message news:[email protected]...

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]

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

[Gord Dibben]

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
<[email protected]> 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

[Gord Dibben]

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
><[email protected]> 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