Google spreadsheet formula to resolve string as a cell

Question

Using google spreadsheets is there a way to evaluate a cell value as the row in a lookup operation? For example rather than =D2 to grab the value of D2, I want to evaluate the value of a cell and use it as the row to lookup another cell. I've looked at the google spreadsheet formula documentation and haven't been able to find a solution.

The below pseudocode illustrates what I'm trying to do.

        A     B    C    D
    1         D
    2       =[B1]2     10
    3       =[B1]3      9
    4       =[B1]4      8

Given the value of B1 is "D" I want cells B2, B3, and B4 to resolve to 10, 9, and 8 respectively.

Solution 1

You might be looking for something like this:

=INDIRECT(INDEX(B$1;1;1)&ROW())

1. the INDEX(B$1;1;1) gets content of B1 cell (the 1;1 is obligatory parameter, since you can feed INDEX with range and means: 1st row in range;1st column in range which is D
2. ROW() returns current row number, be it 2, 3 etc.
3. & concatenates both value, so the result is processed like "D" & "2" => "D2"
4. INDIRECT(reference) returns reference to a cell or an area (in text form) for which to return the contents.

see https://support.google.com/drive/table/25273?hl=en

Still, depending on what is the original problem, there might be better solutions. Above one is just straightforward answer to your question.

Solution 2

Bearing in mind the correctness and helpfulness of PsychoFish's advice on the use of INDEX() with INDIRECT(), and on the limited usefulness of any one solution for all problems, I think the following formula will work as well in this particular case:

=INDIRECT(B$1&Row())

Comments

[PsychoFish]

For case at hand simpler syntax always helps to get the job done more quickly. I just wanted illustrate usage of INDEX and INDIRECT combination in case of more general problem would need to be solved.