Excel - How to concatenate 2 values to make a refference

Question

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")

Answer 1

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

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

Answer 2

If A1 contains "happiness" and B3 contains "A1", then:

=INDIRECT(B3)
=INDIRECT(INDIRECT("B" & 3))
=INDIRECT(INDIRECT(CONCATENATE("B", "3")))

will all return "happiness"

Answer 3

I used the formula:

=INDIRECT(CONCATENATE("Sheet_2!","B",B3))

With which I am concatenating 2 strings and a row, valued in B3.

Answer 4

You may need

=INDIRECT("Sheet_2!" & INDIRECT("B" & "3"))

or the like. Be careful, as

=INDIRECT(INDIRECT("Sheet_2!" & "B" & "3"))

may give a different result. I guess this is the key for what you need.