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
\1
\1
[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
Bookmarks