+ Reply to Thread
Results 1 to 5 of 5

MATCH to find column number, then MATCH *within* that column for different value

  1. #1
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    MATCH to find column number, then MATCH *within* that column for different value

    Hi,

    I am sure what I'm trying to do is really straightforward but I can't get my brain to take that one extra leap!

    Assuming I have a table that looks like this:

    HeaderA HeaderB HeaderC HeaderD HeaderE
    Val1 Val1 Val1 Val2 Val4
    Val2 Val3 Val2 Val3 Val5
    Val3 Val5 Val3 Val4 Val6
    Val4 Val4 Val6 Val5 Val7

    Let's say I need a formula that does the following, in order:

    Checks to see if a separate value (let's call it Alpha) has a match in the top row to find a header value (i.e. HeaderB). This will be used to provide the column number for a second match.
    Checks to see if another separate value (Beta) can be found in that column. This just needs to return a "Yes, there is a match" or "No, this value is not found".


    Example - if Alpha = C and Beta = Val2, it will return "Yes, there is a match". (Formula will look up to find C, then look down that column to find Val2)
    Example - if Alpha = E and Beta = Val 1, it will return "No, there is no match". (Formula will look up to find E, then will not find Val1).

    I've experimented with Index and Match, but I'm not used to doing what is effectively two separate matches in the same formula.

    Very grateful for any help someone could offer!

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: MATCH to find column number, then MATCH *within* that column for different value

    Hello, try this one.

    =IF(COUNTIF(INDEX(A:E,,MATCH(Alpha,A1:E1,0)),Beta),"Yes, there is a match","No, this value is not found")

    Assuming data is in column A:E & header in A1:E1. Change it accordingly.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

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

    Re: MATCH to find column number, then MATCH *within* that column for different value

    Is this what you had in mind?

    Data Range
    A
    B
    C
    D
    E
    1
    HeaderA
    HeaderB
    HeaderC
    HeaderD
    HeaderE
    2
    Val1
    Val1
    Val1
    Val2
    Val4
    3
    Val2
    Val3
    Val2
    Val3
    Val5
    4
    Val3
    Val5
    Val3
    Val4
    Val6
    5
    Val4
    Val4
    Val6
    Val5
    Val7
    6
    7
    8
    9
    Look In
    Look For
    Is It There
    10
    HeaderB
    Val5
    Yes


    This formula entered in C10:

    =IF(COUNTIF(INDEX(A2:E5,,MATCH(A10,A1:E1,0)),B10),"Yes","No")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-06-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: MATCH to find column number, then MATCH *within* that column for different value

    Oh wow, guys! I hadn't even thought of using COUNTIF!

    Both work like a charm - thank you so much for the quick response!

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

    Re: MATCH to find column number, then MATCH *within* that column for different value

    You're welcome. Thanks for the feedback!

+ 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. [SOLVED] Find the row number with a partial match to text in column A
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2014, 01:39 PM
  2. Replies: 5
    Last Post: 02-13-2014, 05:37 PM
  3. Replies: 4
    Last Post: 02-19-2013, 02:19 PM
  4. Replies: 1
    Last Post: 05-06-2011, 03:08 AM
  5. Replies: 4
    Last Post: 12-14-2009, 03:21 PM

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