Search code examples
excelexcel-formulaexcel-2013

Excel - How to concatenate 2 values to make a refference

I have a long column of data (15000 values) that simplified looks like this:

A      B          C         D
1      lorem      pellen    Vestibulum   
2      epsum      tesque    pretium
3      Morbi      vel       convallis
4      fermentum  tellus    nibh
5      Interdum   molestie  Vi
..
15000

Then I have a second table:

A           B            C
TYPE        row_start    row_end
type 1      1            765
type 2      766          468
type 3      312          1789
type 4      7775         1324
type 5      756          9999
...

The second table has all the data; the first row has information, from which row to which row is the data relevant for a particular type. I know on which rows is the information, I just don't know how to reach the data, that is on the same rows, but different columns.

what I know: I know how to use the INDIRECT() function.

what I want: I want to be able to CONCATENATE 2 strings to make a reference: Example: 

INDIRECT(B3)                     <<-- this works of course
INDIRECT(CONCATENATE("B","3"))   <<-- doesn't work
INDIRECT("B"&"3")                <<-- doesn't work

what I don't want To make extra columns, which would exist only for this purpose: i.e.

A           B                     C               D                ...
TYPE        info1_row_start       info1_row_end   info2_row_start  ...
type 1      Sheet_2!C1            Sheet_2!C765    Sheet_2!D1       ...     
type 2      Sheet_2!C766          Sheet_2!C468    Sheet_2!D766     ...
type 3      Sheet_2!C312          Sheet_2!C1789   Sheet_2!D766     ...
type 4      Sheet_2!C7775         Sheet_2!C1324   Sheet_2!D766     ...
type 5      Sheet_2!C756          Sheet_2!C9999   Sheet_2!D766     ...
...

EDIT As answered by Gary's Student, the following both work.

INDIRECT(CONCATENATE("B","3"))
INDIRECT("B"&"3")
Solution

  • Commenting in an answer so as to add a picture. But your second formula also seems to work fine:

    enter image description here

    Note that with a 2 in B3, the formula returns B from Sheet_2!B2