Concatenate a VLoopup Table Arra

[crburke92]

Having issues once it comes time to reference another sheet.

So each device I am trying to find (~300 over 15000 rows) has a specific serial number always in column B. These are on Sheet1. Once that cell is found, there is a set table size I am trying to make, 150 rows long, and extent from B to J.

On sheet2, you enter the Serial Number in C3. Cell C4 has the formula =MATCH(C3,Sheet1!A1:A99999,0) to return what will be the start point of my array.

Below I have the formula =VLOOKUP("(H2)",INDIRECT(CONCATENATE("B",C4,":J",(C4+150))),3,)

When I evaluate the formula,

INDIRECT(CONCATENATE("B",C4,":J",(C4+150))) returns exactly what I want​

INDIRECT("B2:J152")
$B$2:$J$152

The issue is this range needs to be on Sheet1 so:

=VLOOKUP("(H2)",Sheet1!INDIRECT(CONCATENATE("B",C4,":J",(C4+150))),3,)

I've tried a few different ways of doing this:

=VLOOKUP("(H2)",Sheet1!"INDIRECT(CONCATENATE("B",C4,":J",(C4+150)))",3,)
=VLOOKUP("(H2)",INDIRECT(Sheet1!CONCATENATE("B",C4,":J",(C4+150))),3,)

and so on, but keep getting a "There's a problem with the formula".

I should also note that the initial formula I mentioned returns an #N/A as opposed to a formula error.

Is building a table array like this even possible or am I referencing something incorrectly?

Thanks!

 

[steve the fish]

You add the sheet into the indirect:

INDIRECT("'Sheet1'!B"&C4&":J"&C4+150)

 

[crburke92]

Oh, I didn't even need to use CONCATENCATE? Thats awesome, works great!

Thanks

 

[steve the fish]

You could type Sheet1 into a cell and use that if you wanted too.