| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
I'm copying a VLOOKUP formula across a number of columns. How do I make the
formula col_index_num automatically change to the corresponding column cell as I drag? -- Cheers Matt |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
If you want to start with col index 2 then use Column(B1) as the index...
when you drag it to the next column it will change to Column(C1) and give you 3, and so on. -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Matt" wrote: I'm copying a VLOOKUP formula across a number of columns. How do I make the formula col_index_num automatically change to the corresponding column cell as I drag? -- Cheers Matt |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Thanks Sheeloo,
I've either worded my post incorrectly or I might not be understanding your instructions. I'm actually at the other end of the formula. The column number which will contain the result I'm looking up. VLOOKUP($B11,Positions!$A$9:$AR$28,5) It is the "5" I want to change to 6 - 7 and so on... -- Cheers Matt "Sheeloo" wrote: If you want to start with col index 2 then use Column(B1) as the index... when you drag it to the next column it will change to Column(C1) and give you 3, and so on. -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Matt" wrote: I'm copying a VLOOKUP formula across a number of columns. How do I make the formula col_index_num automatically change to the corresponding column cell as I drag? -- Cheers Matt |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
If I wanted it to increment starting from say: 4,
as the VLOOKUP is copied across, then I'd usually use: COLUMNS($A:A)+3 The "+3" would be the simple arithmetic adjustment to suit the starting cell's col_index_num -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Matt" wrote: I'm copying a VLOOKUP formula across a number of columns. How do I make the formula col_index_num automatically change to the corresponding column cell as I drag? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Instead of =VLOOKUP($B11,Positions!$A$9:$AR$28,5)
use VLOOKUP($B11,Positions!$A$9:$AR$28,Column(E1)) and drag right... Column(E1) will evaluate to 5, change to F1 which will evaluate to 6...and so on... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Matt" wrote: Thanks Sheeloo, I've either worded my post incorrectly or I might not be understanding your instructions. I'm actually at the other end of the formula. The column number which will contain the result I'm looking up. VLOOKUP($B11,Positions!$A$9:$AR$28,5) It is the "5" I want to change to 6 - 7 and so on... -- Cheers Matt "Sheeloo" wrote: If you want to start with col index 2 then use Column(B1) as the index... when you drag it to the next column it will change to Column(C1) and give you 3, and so on. -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Matt" wrote: I'm copying a VLOOKUP formula across a number of columns. How do I make the formula col_index_num automatically change to the corresponding column cell as I drag? -- Cheers Matt |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Thanks Max,
Worked well for the first 10 columns, then gave REF errors? -- Cheers Matt "Max" wrote: If I wanted it to increment starting from say: 4, as the VLOOKUP is copied across, then I'd usually use: COLUMNS($A:A)+3 The "+3" would be the simple arithmetic adjustment to suit the starting cell's col_index_num -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Matt" wrote: I'm copying a VLOOKUP formula across a number of columns. How do I make the formula col_index_num automatically change to the corresponding column cell as I drag? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Success. Thanks very much Sheeloo.
-- Cheers Matt "Sheeloo" wrote: Instead of =VLOOKUP($B11,Positions!$A$9:$AR$28,5) use VLOOKUP($B11,Positions!$A$9:$AR$28,Column(E1)) and drag right... Column(E1) will evaluate to 5, change to F1 which will evaluate to 6...and so on... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Matt" wrote: Thanks Sheeloo, I've either worded my post incorrectly or I might not be understanding your instructions. I'm actually at the other end of the formula. The column number which will contain the result I'm looking up. VLOOKUP($B11,Positions!$A$9:$AR$28,5) It is the "5" I want to change to 6 - 7 and so on... -- Cheers Matt "Sheeloo" wrote: If you want to start with col index 2 then use Column(B1) as the index... when you drag it to the next column it will change to Column(C1) and give you 3, and so on. -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Matt" wrote: I'm copying a VLOOKUP formula across a number of columns. How do I make the formula col_index_num automatically change to the corresponding column cell as I drag? -- Cheers Matt |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Worked well for the first 10 columns, then gave REF errors?
That shouldn't be, unless the col_index_num exceeds your vlook's table range Eg in B2: =VLOOKUP($A2,Sheet2!$A:$K,COLUMNS($A:A)+1,0) When you copy B2 across, it'll return ok the values from Sheet2's cols B to K. Beyond that it returns #REF! as the col_index_num would have exceeded the vlook's table range: Sheet2!$A:$K. The correction to then apply, for instance, would be to extend the vlook's table range beyond col K, eg: Sheet2!$A:$Z COLUMNS($A:A)+1 simply returns the series: 1,2,3,... as you copy it across -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Spot on Max. My array table was eceeded. Problem fixed.
Thanks very much. -- Cheers Matt "Max" wrote: Worked well for the first 10 columns, then gave REF errors? That shouldn't be, unless the col_index_num exceeds your vlook's table range Eg in B2: =VLOOKUP($A2,Sheet2!$A:$K,COLUMNS($A:A)+1,0) When you copy B2 across, it'll return ok the values from Sheet2's cols B to K. Beyond that it returns #REF! as the col_index_num would have exceeded the vlook's table range: Sheet2!$A:$K. The correction to then apply, for instance, would be to extend the vlook's table range beyond col K, eg: Sheet2!$A:$Z COLUMNS($A:A)+1 simply returns the series: 1,2,3,... as you copy it across -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Welcome, glad that clarified it.
-- Max Singapore http://savefile.com/projects/236895 Downloads:19,500, Files:362, Subscribers:62 xdemechanik --- "Matt" wrote in message ... Spot on Max. My array table was exceeded. Problem fixed. Thanks very much. -- Cheers Matt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookups | Excel Discussion (Misc queries) | |||
| V & H Lookups | New Users to Excel | |||
| Lookups | Excel Worksheet Functions | |||
| LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
| Lookups | Excel Discussion (Misc queries) | |||
Linear Mode
