Get INDEX array from cell value?

Vintage79

Board Regular
Joined
May 29, 2007
Messages
185
Office Version
  1. 2019
Platform
  1. Windows
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!"
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
secondsheetsecond!D2sheetsecond!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)

<colgroup><col><col><col span="4"><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
you will need to use INDIRECT to turn the text into a reference.


Book1
ABCDEF
1Column1Column2Column3SC15
2header1header2header3
3111
4111
5111
6111
7111
Sheet1
Cell Formulas
RangeFormula
F1=SUM(INDIRECT("tbl"&E1))
 
Upvote 0
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! :)
 
Upvote 0
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


Book1
ABCDEF
1SCHead2
2DATAA1DATAB1
3DATAA2DATAB2
4DATAA3DATAB3
5DATAA4DATAB4SCdataa5
6DATAA5DATAB5
7DATAA6DATAB6DATAB5
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
my solution works fine - check for typos in your formula and for the location of "bingo" in the second sheet
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top