+ Reply to Thread
Results 1 to 4 of 4

Using the MATCH function to look for multiple conditions

  1. #1
    Registered User
    Join Date
    07-29-2013
    Location
    California, United States
    MS-Off Ver
    Excel 2010
    Posts
    49

    Using the MATCH function to look for multiple conditions

    This is my data:

    R1 A x
    R2 A y
    R3 A z
    R4 B x
    R5 B y
    R6 B z


    The letters A and B are in Column G, and the x y z are in Column H. I want to figure out the row that contains both the letters B and y. Not just B or y, but B AND y.

    Assume that "B" is in Cell A1 and "y" is in Cell A2.

    I tried this:

    =MATCH(1,(A1=G1:G6)*(A2=H1:H6),0)

    I should get "5" as an output, but I just get "#VALUE!"

    I also tried this:

    =MATCH(A1&A2,G1:G6&H1:H6,0)

    Still just get "#VALUE!"

    I also tried this:

    =MATCH(A1,(If(H1:H6, G1:G6),0)

    Still fail.

    Lastly, how do you post these excel questions on here without the html code. I can't create the table with the html code.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Using the MATCH function to look for multiple conditions

    Confirm your second formula as an array formula with Ctrl-Shift-Enter instead of just enter.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Registered User
    Join Date
    07-29-2013
    Location
    California, United States
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Using the MATCH function to look for multiple conditions

    Thanks. That worked!

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using the MATCH function to look for multiple conditions

    Do you want the absolute row number or the relative row number? Relative being the row number relative to the location of the lookup values within the referenced range. If your data was in the range G10:H15 the absolute row number would be 14 (the lookup values are physically located on row 14) and the relative row number would be 5 (the lookup values are located on row 5 of the referenced range).

    Here's another one that returns the relative row number...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    B
    A
    x
    2
    Y
    A
    y
    3
    A
    z
    4
    5
    B
    x
    5
    B
    y
    6
    B
    z
    7
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    -----


    This array formula** entered in A4:

    =MATCH(A2,IF(G1:G6=A1,H1:H6),0)

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  2. INDEX/MATCH with Multiple Conditions
    By Lost123 in forum Excel General
    Replies: 4
    Last Post: 03-26-2010, 01:06 PM
  3. multiple lookup with multiple match conditions
    By znaya in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2008, 06:05 AM
  4. Index + Multiple Match Conditions??
    By Spellbound in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-01-2007, 04:07 AM
  5. [SOLVED] How do I add multiple values that match multiple conditions?
    By Joel in forum Excel General
    Replies: 5
    Last Post: 04-10-2006, 08:35 AM

Tags for this Thread

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