Sheet Name But Referencing Column Letter

[emptyq]

Hi,

Overall I'm trying to use a filter function with data from a separate sheet. Problem is the data is not very consistent and the column headers move around. I can use the address and match to find my column letter in the other sheet but I want to be able to put it back in to an overall function like the one at the bottom. It's part of a large function but this is a step I'm stuck on. For reference say data is in Sheet1 and the column with header "Start time" is in column L.

Find "Start time" = Address(1,match("Start time",Sheet1!A1:Z1,0)),4)
Returns L1
If I just want L = SUBSTITUTE(ADDRESS(1,(match("Start time",Sheet1!A1:Z1,0)),4),"1","")

=ArrayFormula(iferror(small(filter(Sheet1!L:L, Sheet1!L:L>0, Sheet1!A:A=A1),1),""))
Sheet1!A:A=A1 does not change just looking up the same value to cross reference rows

My first thought was an indirect function but it doesn't seem to work. Any help is appreciated. I'll update if I find the correct formula or function.

 

[Fluff]

Hi & welcome to MrExcel.
Is this for Excel or for Google sheets?

 

[emptyq]

Oops sorry, just realized I'm on the wrong forum side. Googlesheets so I will delete.

 

[Fluff]

No need to delete I've moved it for you.
Does Sheets allow you to create structured tables, like Excel?

 

[emptyq]

Unfortunately Sheets does not have the same table references as excel. I can manipulate it similar but not the [Table1] style reference.

 

[emptyq]

Worst case I could do all the leg work in excel and reference the returns from there to googlesheets. Say it was in excel, is there a way to do the look up Sheet1!"Lookup Column Letter":"Lookup Column Letter"?

 

[Fluff]

That's a shame because with a table you can directly refer to the column header.
Not sure if this works in Sheets, but you could try

=ArrayFormula(IFERROR(SMALL(FILTER(INDEX(Sheet1!A:Z,,MATCH("Start time",Sheet1!A1:Z1,0)), INDEX(Sheet1!A:Z,,MATCH("Start time",Sheet1!A1:Z1,0))>0, Sheet1!A:A=A1),1),""))

 

[emptyq]

Two things I have found:

1. The small function with n=1 keeps returning 0 (12:00:00AM in my case) for all of my data so using n=2 seems to bypass that.
2. The lookup filter seems to get tripped up on another column with Row 1 header being "Time of last change" and takes that value instead because it is smaller.

Bypassing the small function with n=2 and tightening up my column search width works but it doesn't really help if my L column moves too far up the alphabet.

=ArrayFormula(iferror(small(FILTER(SHEET1!A:S,INDEX(SHEET1!A:S,,MATCH("Start time",SHEET1!$A$1:$S$1,0))<>0, SHEET1!A:A=text(A1,"#")),2),""))

 

[Fluff]

You need to use the index/match twice as I showed, otherwise the small function is working on every column.

 

[emptyq]

Hot dog! For some reason that didn't work in my editing sheet but trying it out in my real one seems to do the trick. Thank you!