2

In Excel, it is possible to use the space character as an intersection operator to obtain the intersection (in terms of constituent cells, not content) of two named ranges, for example:

=Horizontal_Range Vertical_Range

However, in Google Sheets attempting this returns a formula parse error, and online research seems to suggest that the intersection operator (at least in this form) is unavailable in Google Sheets.

Is there an easily readable equivalent in Google Sheets? The initial idea of using this with named ranges was to make the formulae more readable.

Thanks in advance for your help!

Benedikt Wagner
  • 21
  • 1
  • 1
    What is your actual goal? Can you show us your input then desired output? – Harun24hr Mar 13 '23 at 08:40
  • Let's say we have a named range G:G with financial data for the year 2022, named FY_2022 (FY = financial year). We also have a named range 12:12 called Revenue. I want an easily readable way to obtain 2022 revenue. In Excel I would be able to write =Revenue FY_2022 to get the revenue for that year. – Benedikt Wagner Mar 13 '23 at 09:20
  • You may try to use this formula in gs `=INDEX(Inputs_2022,ROW(Revenue))`. – Harun24hr Mar 13 '23 at 09:43
  • I went into the general case of this in some (too much!) detail here... https://stackoverflow.com/questions/52662176/reference-specific-row-in-named-range-within-another-named-range – Tom Sharpe Mar 13 '23 at 15:23

2 Answers2

1

You can use INDEX() function like-

=INDEX(Inputs_2022,ROW(Revenue))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
0

You could also use offset in the event that the ranges weren't complete rows or columns but were still part of a single row and a single column:

=offset(A1,row(Revenue)-1,column(FY_2022)-1)
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37