+ Reply to Thread
Results 1 to 14 of 14

INDEX MATCH Over Seventeen Tables

  1. #1
    Registered User
    Join Date
    01-01-2020
    Location
    Oxford
    MS-Off Ver
    O365 (V2307)
    Posts
    22

    INDEX MATCH Over Seventeen Tables

    Hi,

    I have 17 tables of data - each in exactly the same size and format - the y and x axis have values you read down and along to find the correct value at the intersection of s and y axis. Y is 1-90 in increments of 1, x axis is 1-180 in increments of 5.

    I have another worksheet in the same workbook with 3 cells (A1, B1, C1) containing data that constantly changes. The value in A1 is always 1-17 and tells me which of the 17 tables I need to go to. The values in B1 (y axis) and C1 (x axis) direct me within the correct table to the value in the cell at the relevant intersection of x and y which is the value I need returned.

    So I need a formula that will select the correct table to Index Match Match based on A1, then perform an Index Match Match based on B1 and C1 to return the value I need.

    I have tried all sorts of Index Match with indirect and can't get the formula to work. I was wondering if anyone would mind helping? Is this a job for VBA?
    Last edited by AliGW; 06-03-2020 at 01:07 AM. Reason: Title causing database error - updated.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: INDEX MATCH Over Seventeen Tables

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDEX MATCH Over Seventeen Tables

    INDEX MATCH has a 4th, little known, argument that you can use to determine which table to use, among a bunch of tables. There are also other ways to pick which table to use.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: INDEX MATCH Over Seventeen Tables

    Indeed, and here's a thread that I bookmarked when I first learnt about it: https://www.excelforum.com/excel-gen...nd-weight.html

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: INDEX MATCH Over Seventeen Tables

    With A1 is from 1-17, using user-define-name (Ctrl-F3) to name the relevant table range (from 1-17) then using INDIRECT(A1) to refer to that table, some thing like this:
    Please Login or Register  to view this content.
    Quang PT

  6. #6
    Registered User
    Join Date
    01-01-2020
    Location
    Oxford
    MS-Off Ver
    O365 (V2307)
    Posts
    22

    Re: INDEX MATCH Over Seventeen Tables

    Hi, sample workbook attached with one of the 17 data tables that I am trying to lookup values in.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-01-2020
    Location
    Oxford
    MS-Off Ver
    O365 (V2307)
    Posts
    22

    Re: INDEX MATCH Over Seventeen Tables

    Hi Fdibbins, would you mind sharing the syntax for that 4th argument? I have not come across this deeper magic! What other methods could you use that are non-volatile? I would be really interested to know. I have seen you could use CHOOSE but the formula length gets unwieldy or maybe VBA.

    I have uploaded a sample workbook on this thread with one of the 17 data tables as an example. A1 (Sheet1) tells you which of the 17 tables of data you need to go search in; then B1 and C1 give you the y and x axis reference that locate the cell with the value you need in.

    I have tried formulas like the below and I can't get anything to work across 17 tables. I have tried putting all tables on one sheet. And tried putting all tables in 17 separate sheets.

    =INDEX(INDIRECT("'DataTable"& $A$1 &"'!$1:$1048576"),MATCH($B$1,INDIRECT("'DataTable"& $A$1 &"'!$A:$A"),0),MATCH($C$1,INDIRECT("'DataTable"& $A$1 &"'!$1:$1),0))

    I was wondering if there is a way to use INDEX / COUNTA with each of the MATCH / MATCH to refer to the required reference value in Column B and Row 2 of DataTable1 instead of hardcoding. Column B and Row 2 are in the same position in each table.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH Over Seventeen Tables

    Please try
    =INDEX(INDIRECT("'DataTable"&A1&"'!C3:ZZ999"),MATCH(B1,INDIRECT("'DataTable"&A1&"'!B3:B999")),MATCH(C1,INDIRECT("'DataTable"&A1&"'!C2:ZZ2")))

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: INDEX MATCH Over Seventeen Tables

    Hi Fdibbins, would you mind sharing the syntax for that 4th argument?
    Have a look at the thread linked to in my post above.

  10. #10
    Registered User
    Join Date
    01-01-2020
    Location
    Oxford
    MS-Off Ver
    O365 (V2307)
    Posts
    22

    Re: INDEX MATCH Over Seventeen Tables

    That works, that's really useful thanks. Is there any difference/advantage to having all data tables in separate worksheets vs having all data tables on the same worksheet as separate named tables?

  11. #11
    Registered User
    Join Date
    01-01-2020
    Location
    Oxford
    MS-Off Ver
    O365 (V2307)
    Posts
    22

    Re: INDEX MATCH Over Seventeen Tables

    Hi AliGW, thank you for that. I have gone through that example and can see kind of what they are doing but can't get it to work in my example.

    I have tried something like the below but to no avail!

    =CHOOSE(MATCH(INDIRECT("DataTable"&A1),{INDIRECT("DataTable"&A1)},0),INDEX(INDIRECT("'DataTable"&A1&"'!B3:ZZ999"),MATCH(B1,INDIRECT("'DataTable"&A1&"'!A3:A999")),MATCH(C1,INDIRECT("'DataTable"&A1&"'!B2:ZZ2"))))

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: INDEX MATCH Over Seventeen Tables

    In response to Post #10, you could avoid the use of the volatile INDIRECT function by having all your data tables in one worksheet, arranging them underneath one another with the same spacing between each table (or you could use column A to denote the start of each table). Then the formula would become a simple INDEX/MATCH/MATCH, where the first MATCH term would also include a term to determine the table from which to recover the data.

    Hope this helps.

    Pete

  13. #13
    Registered User
    Join Date
    01-01-2020
    Location
    Oxford
    MS-Off Ver
    O365 (V2307)
    Posts
    22

    Re: INDEX MATCH Over Seventeen Tables

    Hi Pete, thank you for your reply, really useful to get the input. Could I just clarify what you mean by use column A to denote start of each table - make sure the 0 reference for the first row of data in each table starts in Column A each time and all tables line up on top of each other?

    In terms of making it a simple INDEX/MATCH/MATCH would it be possible to jot out a formula that would do this?

    Thanks in advance,
    Matt

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: INDEX MATCH Over Seventeen Tables

    I have partially set this up for you in the attached file.

    I've added two more sets of data tables immediately below the one you already showed, with a single blank row between each one. I've also coloured these differently, for clarity. For simplicity, I've just subtracted 100 from each of the values in the table above. I also removed the blank row above the first table and used column A to denote each table number in turn. You will need to set up all 17 of your data tables like this on the one sheet.

    Then I can use this formula in D1 of Sheet1:

    =IFERROR(INDEX(DataTables!$C:$AL,MATCH($B$1,DataTables!$B$2:$B$92,0)+MATCH($A$1,DataTables!$A:$A,0),MATCH($C$1,DataTables!$C$1:$AL$1,0)),"")

    Note that the first MATCH term finds the row within the table and added to this is the second MATCH term which finds the table number to use. The third MATCH term finds the column from which the data should be returned.

    Your data validation lists do not allow the value zero to be selected for the x or y values, even though the tables include these values.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change data in temporary created file to value
    By MagicMan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2019, 07:48 AM
  2. Change the Name of a Temporary File
    By whisperinghill in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-30-2007, 03:26 PM
  3. Mag - Please change your title
    By mag in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-04-2007, 12:09 PM
  4. Change Title Bar
    By praptisahni in forum Excel General
    Replies: 1
    Last Post: 02-28-2006, 11:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1