Reference Entire Column Using Column Number

[Barklie]

Hello,

I have an inventory data set where new products (columns) are constantly being added. I want a formula that can

1) Find the column of the product I am looking for
2) Return the last value in that column

The match formula does a good job of returning the column number I am looking for:
=MATCH(A11,Records!1:1,0)

And I found a nifty formula for finding the last value in a column:
=LOOKUP(2,1/(Records!L:L<>""),Records!L:L)

The problem is that the second formula uses the "A:A" format when I need it to be able to use the column number instead.

Thanks,
Barklie

 

[Roderick_E]

this is clunky but how about

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") & ":" & SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

Would have to wrap in INDIRECT

 

[FDibbins]

This will give you incremental letters as you copy it across (up to Z)...
=CHAR(65+COLUMNS($A$1:A1)-1)

 

[Barklie]

Thanks Roderick. Unfortunately, I can't get this to work because the INDIRECT function doesn't seem to work with other sheets. The formula below returns the last value in the correct column for the current sheet instead of the "Records" sheet.

=LOOKUP(2,1/(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(A11,Records!1:1,0),4),"1","") & ":" & SUBSTITUTE(ADDRESS(1,MATCH(A11,Records!1:1,0),4),"1",""))<>""),INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(A11,Records!1:1,0),4),"1","") & ":" & SUBSTITUTE(ADDRESS(1,MATCH(A11,Records!1:1,0),4),"1","")))

 

[Barklie]

Thanks for the reply. However, most of the products are beyond column Z, so that formula is a little too limited.

 

[Barklie]

Nevermind, I just had to put the Records! inside of the INDIRECT function. Thanks!!

 

[FDibbins]

This will go beyound z and into AA, AB etc up to ZZ...
=IF(COLUMNS($A$1:A1)<27,"",CHAR(INT(65+(COLUMNS($A$1:A1)-1)/26)-1))&CHAR(65+MOD(COLUMNS($A$1:A1)-1,26))