2

How do I do a simple select on tabular 2D array?

Let's say, I have a named range called "Testy", and that is all the information I have.

  • I know the Columns (dimensions) of the data are in row 0 (or 1, not sure how excel counts rows).
  • I know the data is indexed by date in column/dimension 0 (or 1).

If I need to locate data at "MyColumn", "01/08/2024", how do I make my select?

I've seen a lot of examples using extra information cell like cell address of the headers or the index. Again, the point here is all you know is the name of the named range. You have no idea as to its location, or make up, other than it conforms to tabular format.

I'm guessing something with index like:

=Index(Testy, Match("01/08/2024", Testy[Rows])), Match("MyColumn", Testy[Columns]))

Obviously Testy[Rows] and Testy[Column] have no meaning. If we could index by position we could try something like this:

=Index(Testy, Match("01/08/2024", Testy[Column0])), Match("MyColumn", Testy[Row0]))

But again I've had not luck attempting a syntax that would satisfy that.

Anyone know how to perform these basic selects?

Jamie Marshall
  • 605

1 Answers1

3

If you are using M365 and you want to use INDEX (rather than, say, FILTER), then something like this could work:

=INDEX(
  Testy,
  XMATCH(DATE(2024,1,8), TAKE(Testy,,1)),
  XMATCH("MyColumn", TAKE(Testy, 1))
)

If you don't have the TAKE or XMATCH functions, then something like this:

=INDEX(
  Testy,
  MATCH(DATE(2024,1,8), INDEX(Testy,,1)),
  MATCH("MyColumn", INDEX(Testy,1,))
)
FlexYourData
  • 7,631