Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
Hi,
I have a vlookup which returns the value from a table called data. The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
You could use MATCH on the column headings to return the column
number, so instead of your 3 you would have something like: MATCH(A$1:D$1,"heading",0) If columns are inserted between A and D the formula will automatically adjust. Hope this helps. Pete On Aug 17, 9:17*am, ArcticWolf wrote: Hi, I have a vlookup which returns the value from a table called data. *The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. *This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) * The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
Sorry, I got that the wrong way round. Try it this way:
MATCH("heading",A$1:D$1,0) Hope this helps. Pete On Aug 17, 9:34*am, Pete_UK wrote: You could use MATCH on the column headings to return the column number, so instead of your 3 you would have something like: MATCH(A$1:D$1,"heading",0) If columns are inserted between A and D the formula will automatically adjust. Hope this helps. Pete On Aug 17, 9:17*am, ArcticWolf wrote: Hi, I have a vlookup which returns the value from a table called data. *The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. *This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) * The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
I think u can go for Dsum. For more details about DSUM see help.
-- If this post helps, pls click Yes --------------- TGV "ArcticWolf" wrote: Hi, I have a vlookup which returns the value from a table called data. The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
By mistake i have mentioned DSUM it's not DSUM it's DGET.
-- If this post helps, pls click Yes --------------- TGV "ArcticWolf" wrote: Hi, I have a vlookup which returns the value from a table called data. The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
Both the below formulas return the same;
Col A Col B Col C Col D Rank Name Office 1 Tom R Bath =VLOOKUP(3,A:C,2,0) 2 Katy C Bath =VLOOKUP(3,A:C,MATCH("Name",1:1,0),0) 3 Nigel G Bath 4 Pete R Bath 5 Tony A London 6 John B London 7 Mary C London 8 Jane D London If this post helps click Yes --------------- Jacob Skaria "ArcticWolf" wrote: Hi, I have a vlookup which returns the value from a table called data. The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
Thanks for the quick response Pete. Can I be a pain and ask you to amend my
original vlookup as I'm having difficulty placing the MATCH function into it without getting an error. I'm trying to replace the "3" with the match function, is this correct? Thanks, AW "Pete_UK" wrote: You could use MATCH on the column headings to return the column number, so instead of your 3 you would have something like: MATCH(A$1:D$1,"heading",0) If columns are inserted between A and D the formula will automatically adjust. Hope this helps. Pete On Aug 17, 9:17 am, ArcticWolf wrote: Hi, I have a vlookup which returns the value from a table called data. The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with different col_index_num
Yes, have a look at Jacob's solution.
I can amend your formula if you post it here, and if you can give me details of the cell references used for your table. Pete On Aug 17, 10:52*am, ArcticWolf wrote: Thanks for the quick response Pete. *Can I be a pain and ask you to amend my original vlookup as I'm having difficulty placing the MATCH function into it without getting an error. *I'm trying to replace the "3" with the match function, is this correct? Thanks, AW "Pete_UK" wrote: You could use MATCH on the column headings to return the column number, so instead of your 3 you would have something like: MATCH(A$1:D$1,"heading",0) If columns are inserted between A and D the formula will automatically adjust. Hope this helps. Pete On Aug 17, 9:17 am, ArcticWolf wrote: Hi, I have a vlookup which returns the value from a table called data. *The column I want from the table is column 3, so =vlookup(a1,data,3,false) However, the table will expand over the coming months with new columns inserted before the 3rd column in the data table. *This will make my lookup incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) * The column heading will always remain the same (if that is of use?), so how can I get Excel to find the column I need as opposed to using the col_index_num? TIA, AW- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and using a Name in the col_index_num | Excel Worksheet Functions | |||
changing the vlookup col_index_num | Excel Worksheet Functions | |||
VLOOKUP need to increment col_index_num | Excel Discussion (Misc queries) | |||
vlookup - Col_index_num | Excel Discussion (Misc queries) | |||
Vlookup Col_index_num | Excel Discussion (Misc queries) |