Dynamically form a cell value in Google Sheets

Question

This might be very trivial, but I was not able to obtain any concrete result from my Google searches, so asking here.

I want to fill a column with the content of some other column (in some other sheet). The value at the existing place is not organised to the current requirements, so I just can't directly assign it to the new column. I have another column that specifies the ordering for the new column. So, I want to create my new column with the help of this ordering.

In short, I was wishing to assign my new column as sheet1!A${sheet2!A1} (this doesn't work).

Say, if I have value 23 at sheet2!A1, then the corresponding value in the new column should be of the cell sheet1!A23.

Answer 1

You need to use the INDIRECT function

Try the following in cell A1 of Sheet1

=INDIRECT("Sheet2!A"&A1)

The result would be the value of cell A23 of Sheet1 in cell A1 in Sheet1

Answer 2

This sort of thing is usually done with sort(), as in:

=sort(Sheet1:A1:A, Sheet2:A1:A, true)

This is an array formula that creates the whole result column in one go.

In your case, things are made a little more complex by the fact that the data column and the index column are in different sheets. This will pose a problem in the event the number of rows differs between the two sheets.

The easy solution is to move the index column to the same sheet as the data column. If that is not possible for some reason, you can constrain the two columns to the same height like this:

=sort(
  array_constrain(Sheet1:A1:A, min(rows(Sheet1:A1:A), rows(Sheet2:A1:A)), 1), 
  array_constrain(Sheet2:A1:A, min(rows(Sheet1:A1:A), rows(Sheet2:A1:A)), 1), 
  true 
)

If you need more help, please share a publicly editable sample spreadsheet with realistic-looking data.