Dave,

Thanks for the help! Your code works like a charm.

YH

dim M as Variant 'could be an error
dim B as Variant 'could be an error
dim LastRow as long
dim LastCol as long

Dim myTable as range
with worksheets("data")
'I used column A and Row 1
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set mytable = .range("a1", .cells(lastrow,lastcol))

m = application.match("Q106A",mytable.columns(1),0)
B = application.match(.range("a3").value,mytable.rows( 1),0)

if iserror(M) _
or iserror(b) then
msgbox "At least one thing didn't match
else
msgbox mytable(b,m).value
end if

"Dave Peterson" wrote:

You shouldn't get 0 as the result of using =match(). It'll return a number
between 1 and the size of the range/array if there is a match--this version
(application.worksheetfunction.match()) will cause the code to blow up.

If you've surrounded your code like:

on error resume next
M = Application.WorksheetFunction.Match("Q106A", COL_Key, 0)
B = Application.WorksheetFunction.Match(A3, ROW_KEY, 0)
on error goto 0

Then M and B won't change if there is an error. If the values of M and B were 0
before (uninitialized Longs???), then they'll still be 0s if there is no match.

ps.

A3 looks kind of weird he
B = Application.WorksheetFunction.Match(A3, ROW_KEY, 0)

If A3 is a variable, ok. But if A3 is a cell address, you may want to change to
something like:

B = Application.WorksheetFunction.Match _
(worksheets("somesheet").range("A3").value, ROW_KEY, 0)

Personally, I like to use application.match() and check to see if an error was
returned--not see if the code raised an error.

dim M as Variant 'could be an error
dim B as Variant 'could be an error
dim LastRow as long
dim LastCol as long

Dim myTable as range
with worksheets("data")
'I used column A and Row 1
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set mytable = .range("a1", .cells(lastrow,lastcol))

m = application.match("Q106A",mytable.columns(1),0)
B = application.match(.range("a3").value,mytable.rows( 1),0)

if iserror(M) _
or iserror(b) then
msgbox "At least one thing didn't match
else
msgbox mytable(b,m).value
end if



I did use column A and row 1 to as headers. Not quite sure how your data is
laid out, though.




YH wrote:

I have a table like below and want to find the value of the intersect cell
for a given month and product. I want to use the Index and Match functions to
find the value of the cell, but I am not familar with the usage and am not
able to get Match function to return me the correct row or column location.

1) What did I do wrong?
2) Is there an easier way to find the value of the cell with a matched month
name in a row and product name in a column? Thanks!

Jan Feb Mar Apr...
product a
b
c
d
.
.
.

Worksheets("Data").Names.Add Name:="TABLE", RefersTo:=Range("$A$1", lastRG)
Worksheets("Data").Names.Add Name:="ROW_KEY", RefersTo:=Range("$A$1", "$A$"
& numRow)
Worksheets("Data").Names.Add Name:="COL_KEY", RefersTo:=Range("$A$2",
Cells(2, numCol))

M = Application.WorksheetFunction.Match("Q106A", COL_Key, 0)
B = Application.WorksheetFunction.Match(A3, ROW_KEY, 0)

'I got M=0 and B=0.

--

Dave Peterson