+ Reply to Thread
Results 1 to 7 of 7

Index & Match Function

  1. #1
    YH
    Guest

    Index & Match Function

    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

  2. #2
    Dave Peterson
    Guest

    Re: Index & Match Function

    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.




    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

  3. #3
    YH
    Guest

    Re: Index & Match Function

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


  4. #4
    YH
    Guest

    Re: Index & Match Function

    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

    "YH" wrote:

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


  5. #5
    Dave Peterson
    Guest

    Re: Index & Match Function

    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.

    YH wrote:
    >
    > 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
    >
    > "YH" wrote:
    >
    > > 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 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.
    > > >
    > > >
    > > >
    > > >
    > > > 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
    > > >


    --

    Dave Peterson

  6. #6
    YH
    Guest

    Re: Index & Match Function

    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" wrote:

    > 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.
    >
    > YH wrote:
    > >
    > > 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
    > >
    > > "YH" wrote:
    > >
    > > > 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 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.
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > 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
    > > > >

    >
    > --
    >
    > Dave Peterson
    >


  7. #7
    Dave Peterson
    Guest

    Re: Index & Match Function

    Post back if you have trouble modifying that last code.

    YH wrote:
    >
    > 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" wrote:
    >
    > > 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.
    > >
    > > YH wrote:
    > > >
    > > > 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
    > > >
    > > > "YH" wrote:
    > > >
    > > > > 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 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.
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > > > > 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
    > > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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