Forum Discussion

boukasa's avatar
boukasa
Brass Contributor
Oct 03, 2021
Solved

Adding a column to an array reference

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! 

  • 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)

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    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)

Resources