| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, Column Index Num and Autofill
If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
Use the formula in Col B
=Vlookup($A3,$C$1:$F$12,COLUMN(),false) If the formula is in Col B then it is same as =Vlookup($A3,$C$1:$F$12,2,false) since COLUMN() evaluates to 1 (in A), 2 (in B), 3 (in C), ... depending upon the column the formula in is btw Vlookup($A3,$C$1:$F$12,1,false) should give you circular reference error if enterd in Col A "mp" wrote: If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
The lookup is pointing to another worksheet and the columns, of course, don't
line up for this formula to work correctly. "Sheeloo" wrote: Use the formula in Col B =Vlookup($A3,$C$1:$F$12,COLUMN(),false) If the formula is in Col B then it is same as =Vlookup($A3,$C$1:$F$12,2,false) since COLUMN() evaluates to 1 (in A), 2 (in B), 3 (in C), ... depending upon the column the formula in is btw Vlookup($A3,$C$1:$F$12,1,false) should give you circular reference error if enterd in Col A "mp" wrote: If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
Let's assume you enter the first formula in cell A1.
=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0) Copy across as needed -- Biff Microsoft Excel MVP "mp" wrote in message ... If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
The lookup string is looking for data on another worksheet. This formula
didn't work. "T. Valko" wrote: Let's assume you enter the first formula in cell A1. =VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0) Copy across as needed -- Biff Microsoft Excel MVP "mp" wrote in message ... If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
Just add the sheet name:
=VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0) -- Biff Microsoft Excel MVP "mp" wrote in message ... The lookup string is looking for data on another worksheet. This formula didn't work. "T. Valko" wrote: Let's assume you enter the first formula in cell A1. =VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0) Copy across as needed -- Biff Microsoft Excel MVP "mp" wrote in message ... If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
using your format here's the actual formula:
=IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,C OLUMNS($S13:S13),FALSE)),"",(VLOOKUP($B19,History1 !$B$13:$IV$1499,COLUMNS($S13:S13),FALSE))) Problem - no matter what range I put in it always returns the same value. The info I'm looking for is in S13. The info it's pulling comes from A13. I appreciate the help. "T. Valko" wrote: Just add the sheet name: =VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0) -- Biff Microsoft Excel MVP "mp" wrote in message ... The lookup string is looking for data on another worksheet. This formula didn't work. "T. Valko" wrote: Let's assume you enter the first formula in cell A1. =VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0) Copy across as needed -- Biff Microsoft Excel MVP "mp" wrote in message ... If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup, Column Index Num and Autofill
History1!$B$13:$IV$1499
The info I'm looking for is in S13 Ok, but that's not what you demonstrated in your original post. You demonstrated that you want the results starting from the 1st column of the lookup table and then incrementing as you copy across. S13 would be column number 18 *relative* to your lookup table. If you want the first result to come from column 18 of the lookup table: =IF(COUNTIF(History1!$B$13:$B$1499,$B19),VLOOKUP($ B19,History1!$B$13:$IV$1499,COLUMNS($B:S),0),"") The first result will come from column S. As you copy across the results will come from columns T, U, V, W, etc., etc. no matter what range I put in it always returns the same value. Make sure you have calculation set to automatic. ToolsOptionsCalculation tabAutomaticOK -- Biff Microsoft Excel MVP "mp" wrote in message ... using your format here's the actual formula: =IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,C OLUMNS($S13:S13),FALSE)),"",(VLOOKUP($B19,History1 !$B$13:$IV$1499,COLUMNS($S13:S13),FALSE))) Problem - no matter what range I put in it always returns the same value. The info I'm looking for is in S13. The info it's pulling comes from A13. I appreciate the help. "T. Valko" wrote: Just add the sheet name: =VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0) -- Biff Microsoft Excel MVP "mp" wrote in message ... The lookup string is looking for data on another worksheet. This formula didn't work. "T. Valko" wrote: Let's assume you enter the first formula in cell A1. =VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0) Copy across as needed -- Biff Microsoft Excel MVP "mp" wrote in message ... If I have a vlookup in one cell and I try to autofill across multiple columns, how do get the column index num reference to increase by one as I autofill across. It appears as though the column index number is always an absolute value. Example Column A Column B Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I automatically update column index number in VLookup whe. | Excel Worksheet Functions | |||
| VLOOKUP Column Index | Excel Discussion (Misc queries) | |||
| vlookup column index number argument | Excel Worksheet Functions | |||
| Multiple Column Index Number in VLookup | Excel Worksheet Functions | |||
| vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
Hybrid Mode
