SOLVED

Working with Arrays

New Contributor

There may well be a simple solution to this, but I've search far and wide and can't find how Excel can achieve this outcome.

I have an array of numbers stored in A1:G10.

I'm trying to retrieve the contents of a single cell where the row and column values are generated automatically.

For example if I generate a Row value of one (3) and a Column value of four (4) I'd like to retrieve the data from cell D3.

Hope that makes sense, and thank you for any insights.

Rod

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Rod_Sims The basic formula would look like this:

=INDEX(A1:G10,3,4)

where you can replace the 3 and 4 with references to cells that contain these numbers. 

@Riny_van_Eekelen 

Thank you so much - an elegant and simple solution to what I had thought might be more complex. I've got my formulas working. Again, thank you!

@Riny_van_Eekelen 

For your amusement, something I occasionally do is to wrap the INDEX function within a Lambda function to create a more conventional programming syntax

 

Worksheet formulae 
= MyArray(3,4)
= MyArray(3, )
= MyArray( ,4)
= MyArray( )

MyArray 
= LAMBDA([r], [c], 
    INDEX(Sheet1!$A$1:$G$10, r, c)
  );