+ Reply to Thread
Results 1 to 2 of 2

Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??

    Hello All,

    I am trying to use INDEX MATCH MATCH with TABLE NAMES instead of by entering the cell numbers. I want it to be a dynamic spreadsheet so that there's no need to alter anything if any new additions to the table are made. I understand how to use INDEX MATCH MATCH with cell numbers, and I've gotten it to work that way, but that would require altering the formulas themselves.

    I have also found an old thread that shows how to use the RANGE NAME function to do this, but this would require altering the name manager names as changes were made to the table. What I'm trying to do is use the TABLE NAMING language to get the index match match to work, so it will be truly dynamic to new additions.

    I'm having trouble posting excel file, will try from a different location later on tonight. For now here's a foto

    http://imageshack.com/a/img811/2070/ys0z.png

    http://imagizer.imageshack.us/v2/xq90/811/ys0z.png

    http://imageshack.com/a/img811/2070/ys0z.png

    [IMG=http://imagizer.imageshack.us/v2/xq90/811/ys0z.png][/IMG]


    CELL NUMBERS
    =INDEX(B15:F23,MATCH(B7,B15:B23,0),MATCH(C7,B15:F15,0))
    YES, THIS WORK

    RANGE NAMES
    =INDEX(MyTable,MATCH($C6,MyTable_ValueB,0),MATCH($B6,MyTable_ValueA,0))
    YES, THIS WORK

    TABLE NAMES
    =INDEX(Office_List_Body,MATCH(D3,Office_List_Table_Verticle,0),MATCH(D2,Office_List_Table_Horizontal,0))
    NO, THIS DOESN'T WORK WORK WORK!!!

    In the first sheet of the attached spreadsheet I tried using the (static) cell lookup on a regular table and it doesn't work. Do I have to convert the table to range in order to use the index match match function??



    http://www.excelforum.com/excel-gene...ange-name.html

    [QUOTE]I guess what you want to do is make 3 names for each table

    MyTable
    table

    TableValueA
    =offset(mytable,0,0,1,columns(Mytable))

    TableValueB
    =offset(MyTable,0,0,rows(Mytable),1)

    then you can do

    Index(Mytable,match(ValueB,TablevalueB,0),match(valueA,tablevalueA,0))

    If your table changes just change MyTable to reference the new table limits! [/QUOT

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??

    I've had no problem using structured table references in formulas. The example you posted does not contain valid structured table references.
    Try this...
    Instead of typing references...when you build your formula, select the table sections you want to reference.

    Example:
    With this data in a table named tblMyTable
    Please Login or Register  to view this content.
    And
    A1: Bravo

    This regular formula returns 963
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ 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. [SOLVED] Index Match Formula doesn't recognize output from Right Formula as lookup value.
    By GoGators in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2015, 11:33 AM
  2. Index Match formula doesn't seem to be working
    By ortmandj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2013, 08:04 PM
  3. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  4. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  5. Replies: 5
    Last Post: 01-06-2012, 08:00 PM

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