Index & Match Function

[Guest]

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

 

[Dave Peterson]

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 here:
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.

 

[Guest]

Dave,

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

YH

 

[Guest]

Question:

If I want to loop through all rows and columns in myTable, find the matched
intersects, and copy the matched intersect cell values to another worksheet.

What will be a good way to do it?

Thanks,

YH

 

[Dave Peterson]

I don't understand.

If you want to copy the table, then just copy the table all at once.

If you have another worksheet that has (say) a list of row values and a list of
column values and you wanted to return the intersection, then that kind of makes
sense to me.

dim M as Variant 'could be an error
dim B as Variant 'could be an error
dim myRng as range
dim myCell as range
dim LastRow as long
dim LastCol as long
Dim myTable as range
dim myRes as variant

with worksheets("othersheet")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

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
end with

for each mycell in myrng.cells
m = application.match(mycell.value,mytable.columns(1),0)
B = application.match(mycell.offset(0,1).value,mytable.rows(1),0)

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

mycell.offset(0,2).value = myres
next mycell

Untested, uncompiled. Watch for typos!

This looks down one column (column A in OtherSheet) and looks for a row match.
And looks at column B in Othersheet for a columns match.

Then it puts the result in column C in Othersheet.

 

[Guest]

Thanks for the code. Here is more detail about my task:

I want to find the value of a particular cell in a table in a WorksheetA
based on a pair of identified row and col values, and copy the value of the
intersection to a designated cell location in a different table in
WorkSheetB.

In a larger scope, WorksheetA has the master data of all products. I will
need to create a worksheet for each product and copy information from the
master data WorksheetA to a standardized table in each worksheet. This
process needs to be repeated for all product worksheets.

I was trying to find an easier way to automate this. Your code has given me
some clues.

Thanks,

YH

 

[Dave Peterson]

Post back if you have trouble modifying that last code.

Thanks for the code. Here is more detail about my task:

I want to find the value of a particular cell in a table in a WorksheetA
based on a pair of identified row and col values, and copy the value of the
intersection to a designated cell location in a different table in
WorkSheetB.

In a larger scope, WorksheetA has the master data of all products. I will
need to create a worksheet for each product and copy information from the
master data WorksheetA to a standardized table in each worksheet. This
process needs to be repeated for all product worksheets.

I was trying to find an easier way to automate this. Your code has given me
some clues.

Thanks,

YH