Correct Row/Column Numbers with INDEX and MATCH

[Opv]

Is there a way to force the INDEX and MATCH functions to return the correct row and column numbers in a table that does not begin in row 1, column 1? Unless I am missing something, they seems to be returning the row/column numbers of the worksheet rather than of the table of data.

Currently, I'm testing a table using a named range for the table of data (which includes the row and column labels as well as the table data), and I have also named the ranges for the column/row labels. I'm running Excel 2000 on a Windows 7 machine. Thanks

[SamT]

AFAIK, Index only returns cell values or references to the Cells themselves.
Ie. assuming that D24 has the value 42 in it index will return either 42 or is the same as using D24 in the given formula

See Match below

Sheet to table reference:
Given Table = Sheet Range ("B2:X99"), where Sheet Range("B2") is empty

Table Range("A1") is empty.
Column Labels are in Sheet Row 2(Range"C2:X2") , Table Row 1(Table Range ("B1:W1")
Row Labels are in Sheet Column B(Range("B3:B99"), Table Column A (Table Range("A2:A98")


This might work, I've never tried.
Assume that the column Label in the top row, third column of the Table, ("MyTable",) is "ThisCol". That is Sheet Range("D2")
In Sheet Range("B5") is "ThisRow"

MyColNum = Match("ThisCol",MyTable(B1:W1),0)
MyCol should = 3

MyRow = Match("ThisRow",MyTable(B1:B98),0)
ThisRow should = 4

With named Ranges, you can also just use =Column("ThisCol")-Column("MyTable") and =Row("ThisRow")-Row("MyTable")

How do you intend to use these row and column numbers once you have them. There are many different ways to accomplish what you need to do without the actual row and column number numbers.

[SamT]

OPV, if you found the solution, please share it with our guests.
Thanks

[Opv]

I would be happy to share what I did if I knew what I was. I was just tinkering around with the named ranges and all of a sudden it started returning the correct values. I guess I just didn't initially have my named ranges properly defined.

[SamT]

IT happens to all of us. Thanks for sharing.