RC notation in IMPORTRANGE

Question

Is there a way to use RC notation in the IMPORTRANGE function? What I need to do is to import a certain column, which I'm finding using MATCH in the first row (headers)

=importrange("linktosheet","sheet!c" & match(importrange("linktosheet","sheet!1:1"),"headername") ) <- which would return the n-th column

I tried to use CHAR to get the column letter from the MATCH value, but it means I have to repeat the MATCH function twice; plus, it works up to column Z; I would need to add another function to do two-letter columns.

Another way would be to use hlookup or something, but I'm afraid that would be very inefficient.

Answer 1

Use lambda() to avoid repeating importrange(), and filter() to get just the column(s) that match headerName, like this:

=lambda( 
  data, headerName, 
  filter( data, match(array_constrain(data, 1, 999), headerName, 0) ) 
)(
  importrange("linktosheet", "sheet!A1:ZZZ"), "headername" 
)