Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |