Vlookup help

S

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
 
D

David Biddulph

shelly2 said:
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.
Click to expand...

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

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!
=====================================================

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
 
S

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
 
G

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
 
G

Gord Dibben

Shelly

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

No need for the UDF


Gord
 
Top