+ Reply to Thread
Results 1 to 4 of 4

2 variables in a function

  1. #1
    Registered User
    Join Date
    01-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    12

    2 variables in a function

    Hey all, ive got this code atm

    =(INDEX(Data!$C$368:$C$394,MATCH($E$2&CHAR(127)&$A7,Data!$C$2&CHAR(127)&Data!$B$368:$B$532,0),0))

    I was wondering if it was possible to change one part of the match function to search for 2 variables. As in instead of only having $E$2 i want it to search for $E$2 and $E$3 .

    Thanks

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: 2 variables in a function

    MATCH can search for a single value only. One solution is to create a formula that tests each possibility separately, but the way that works is to first search for one value, then if it's not found search for the other. From your description you may want to find the first match to either value, so this may not work for you. It gets long, and I haven't tested this to see if it works, only that it is a legal formula:
    Please Login or Register  to view this content.
    The other solution would be to write some VBA to either create a UDF, or just provide the value into the cell. However, the Range.Find method will search only for one value as well so the search would have to be coded manually as a For Each loop for every cell in the range.

    I would personally code a general-purpose UDF called something like MULTIMATCH that takes multiple parameters for the value to match.
    [/COLOR][/SIZE][/FONT]
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: 2 variables in a function

    MATCH returns #N/A if not found (rather than 0) so I think it needs to look like this:
    Please Login or Register  to view this content.

    The other solution would be to write some VBA to either create a UDF, or just provide the value into the cell. However, the Range.Find method will search only for one value as well so the search would have to be coded manually as a For Each loop for every cell in the range.

    I would personally code a general-purpose UDF called something like MULTIMATCH that takes multiple parameters for the value to match.
    Last edited by 6StringJazzer; 01-28-2011 at 10:17 AM.

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: 2 variables in a function

    Are you summing E2 and E3 or merely concatenating them? Is it an And scenario or an Or scenario?

    SUMPRODUCT may be the solution, but without further explaination and/or an example workbook, it's just a guess.

+ 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