Error Checking in Excel
Results 1 to 7 of 7

Thread: Get INDEX array from cell value?

  1. #1
    Board Regular Vintage79's Avatar
    Join Date
    May 2007
    Location
    London, UK
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!"
    I am currently using Excel 2010 and Windows 10 Pro (64-bit)
    _______________________________________________________________


    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,431
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #3
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,116
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Get INDEX array from cell value?

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

    ABCDEF
    1Column1Column2Column3SC15
    2header1header2header3
    3111
    4111
    5111
    6111
    7111

    Sheet1



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

    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  4. #4
    Board Regular Vintage79's Avatar
    Join Date
    May 2007
    Location
    London, UK
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    I am currently using Excel 2010 and Windows 10 Pro (64-bit)
    _______________________________________________________________


    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,116
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    1SCHead2
    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.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  6. #6
    Board Regular Vintage79's Avatar
    Join Date
    May 2007
    Location
    London, UK
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Last edited by Vintage79; Mar 11th, 2018 at 05:09 PM.
    I am currently using Excel 2010 and Windows 10 Pro (64-bit)
    _______________________________________________________________


    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,431
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •