# Thread: Get INDEX array from cell value?

1. ## Get INDEX array from cell value?

Hello!

I have several data tables that are named tblRT, tblSC, tblPF etc. I then have a sheet where a user can enter the 2-digit table identifier (RT, SC, PF etc) and I would like to use this information in a formula.

I am using this formula, which works fine: =INDEX(tbl SC ,MATCH(F5,tbl SC [SC],0),2) but I w ould like the 'SC' to be collected from the cell that the user has put it in.

In other words, the text 'SC' is in cell A1, and I want to add this into the formula, so that it will look like this:
=INDEX(tbl&"text from A1",MATCH(F5,tbl&"text from A1"["text from A1"],0),2)

I'd appreciate it if anyone knows how to do this. Otherwise, I may have to examine it from another angle.

Thanks!"

2. ## Re: Get INDEX array from cell value?

 second sheetsecond ! D2 sheetsecond!D2 bingo this sheet is sheetfirst in cell D2 of sheetsecond there is the word bingo the user puts second into cell A1 of this sheet B2 adds the word in A1 to sheet C1 is ! D1 contains the target cell in sheetsecond G1 concatenates B1,C1,D1 cell G6 containing bingo has the formula =INDIRECT(G1)

3. ## Re: Get INDEX array from cell value?

you will need to use INDIRECT to turn the text into a reference.

ABCDEF
1Column1Column2Column3SC15
3111
4111
5111
6111
7111

Sheet1

Worksheet Formulas
CellFormula
F1=SUM(INDIRECT("tbl"&E1))

4. ## Re: Get INDEX array from cell value?

Thanks a lot for the responses!

oldbrewer, I tried your method but I couldn't get it to work. G6 returned #REF !. But I understood the theory behind it.

Scott, I also tried your formula - this adds up all the values in the range that I want to return a value from. But I don't think I'll be able to use it for what I need.

It's clear that INDIRECT is the way to go, but I cannot see how to make it work.

This formula: ="tbl"&INDIRECT("E5") will return tlbSC, as cell E5 contains 'SC'. This is exactly what I want, so.....

I tried making this change to my original formula:

=INDEX(tblSC,MATCH(F5,tblSC[SC],0),2)
=INDEX("tbl"&INDIRECT("E5"),MATCH(F5,tblSC[SC],0),2)

This returns #VALUE ! and I cannot figure out why!

5. ## Re: Get INDEX array from cell value?

The entire reference needs to be in the indirect function. The entire table name and the column header, if it is part of the reference it needs to be in the indirect

something like

ABCDEF
2DATAA1DATAB1
3DATAA2DATAB2
4DATAA3DATAB3
5DATAA4DATAB4SCdataa5
6DATAA5DATAB5
7DATAA6DATAB6DATAB5

Sheet1

Worksheet Formulas
CellFormula
F7=INDEX(INDIRECT("tbl"&E5),MATCH(F5,INDIRECT("tbl"&E5&"[SC]"),0),2)

Note: if you are using indirect to referring to something in a different workbook the other workbook must be open. It does not like closed workbooks.

6. ## Re: Get INDEX array from cell value?

Thanks again for both responses advising me to use INDIRECT. I have finally found a solution to the problem by using a combination of VLOOKUP and the ideas suggested here.

Here is the formula: =INDEX(INDIRECT(VLOOKUP(E5,tblINDEX,2,FALSE)),MATCH(F5,INDIRECT(VLOOKUP(E5,tblINDEX,2,FALSE)&"["&INDIRECT("E5")&"]"),0),2)

I created another table for VLOOKUP (called tblINDEX) to search, with all the table names in, and I've managed to substitute this with the SC and tblSC etc.

For comparison with the original formula, in case anyone searches for this problem later:
=INDEX(tblSC,MATCH(F5,tblSC[SC],0),2)
tblSC is replaced with INDIRECT(VLOOKUP(E5,tblINDEX,2,FALSE))
[SC] is made up with &"["&INDIRECT("E5")&"]"

So, this formula works, but now there is a new problem (isn't there always?? ). Hopefully this problem will be easier to solve than the last: When this formula gets copied down my table, one cell reference does not change, because it is in inverted commas, and seen as text by Excel.

=INDIRECT("E5") Is there a way of making the E5 change to E6 when I copy it down?

7. ## Re: Get INDEX array from cell value?

my solution works fine - check for typos in your formula and for the location of "bingo" in the second sheet