SOLVED

Adding a column to an array reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2807556%22%20slang%3D%22en-US%22%3EAdding%20a%20column%20to%20an%20array%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807556%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%2C%20say%20Things%2C%20with%20two%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20a%20reference%20to%20this%20table%20elsewhere%20in%20the%20workbook%2C%20%3DThings%2C%20and%20the%20table%20spills%20into%20the%20cells%20there%20in%20two%20columns%20-%20an%20exact%20reference%20to%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20want%20to%20add%20a%20column%20next%20to%20this%20spilled%20data%20that%20performs%20a%20lookup%20on%20the%20values%20in%20the%20second%20column%20of%20the%20spilled%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20implement%20this%20so%20that%20the%20calculated%20column%20is%20also%20an%20array%20formula%2C%20so%20it%20automatically%20expands%20downward%20next%20to%20the%20spill%2C%20as%20items%20are%20added%20to%20the%20original%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20don't%20want%20to%20add%20the%20column%20to%20the%20original%20table%20for%20a%20variety%20of%20reasons.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2807556%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2807680%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20a%20column%20to%20an%20array%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173763%22%20target%3D%22_blank%22%3E%40boukasa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEither%20refer%20to%20the%20original%20column%20or%20use%20INDEX()%20to%20get%20the%20column%20data.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(Things%5BColumn1%5D%2Clookup_table%2C2%2CFALSE)%0A%0A%3DVLOOKUP(INDEX(F2%23%2C0%2C1)%2Clookup_table%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2807719%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20a%20column%20to%20an%20array%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807719%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a table, say Things, with two columns.

 

I use a reference to this table elsewhere in the workbook, =Things, and the table spills into the cells there in two columns - an exact reference to the table.

 

I now want to add a column next to this spilled data that performs a lookup on the values in the second column of the spilled cells. 

 

Is there a way to implement this so that the calculated column is also an array formula, so it automatically expands downward next to the spill, as items are added to the original table?

 

(I don't want to add the column to the original table for a variety of reasons.)

 

Thanks! 

2 Replies
best response confirmed by boukasa (Occasional Contributor)
Solution

@boukasa 

Either refer to the original column or use INDEX() to get the column data.

=VLOOKUP(Things[Column1],lookup_table,2,FALSE)

=VLOOKUP(INDEX(F2#,0,1),lookup_table,2,FALSE)
Thank you very much.