+ Reply to Thread
Results 1 to 16 of 16

Index/Match question

  1. 06-22-2019, 10:25 AM #1
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Index/Match question 

    Please Login or Register  to view this content.

    I use this formula to match single numbers in P2 with the defined range. Is it possible to change this formula so that it works when there are comma separated 2 or 3 numbers in P2. And if one (or more) of these numbers in P2 match with the defined range, it will be indexed in the target cell.

    I need a formula solution. If it requires VBA, it has to be with a user defined formula, please. (Without user defined formula, it won't be useful for me).

    Thanks.

    I asked this question https://www.mrexcel.com/forum/excel-...-question.html too but I couldn't get an answer yet.
    Last edited by AliGW; 06-22-2019 at 11:03 AM.
    Reply With QuoteRegister To Reply
  2. 06-22-2019, 10:45 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Index/Match question

    Administrative Note:

    I see from your cross post that you are expecting a speedy response
    . Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.
    Reply With QuoteRegister To Reply
  3. 06-22-2019, 10:53 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,709

    Re: Index/Match question

    Where has this been cross-posted, please?

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 06-22-2019, 10:54 AM #4
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    Thanks, AliGW. But I have already provided the link to the cross post. https://www.mrexcel.com/forum/excel-...-question.html
    Reply With QuoteRegister To Reply
  5. 06-22-2019, 10:56 AM #5
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    Thanks, Pepe. you are right, and since today is saturday, not many people may have time to help.
    Reply With QuoteRegister To Reply
  6. 06-22-2019, 11:00 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,709

    Re: Index/Match question

    Max - where is the link in your opening post? I don't see it.
    Reply With QuoteRegister To Reply
  7. 06-22-2019, 11:01 AM #7
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    You can see it when you scroll over "here" or click "here" in the original post. It's hidden link.
    Reply With QuoteRegister To Reply
  8. 06-22-2019, 11:04 AM #8
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Index/Match question

    I can confirm that, I didn't see it either at first
    Reply With QuoteRegister To Reply
  9. 06-22-2019, 11:04 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,709

    Re: Index/Match question

    That's barely visible - I've made it more obvious. Please, if you do this in future, make the link very obvious. Don't try and hide it! Thanks.
    Reply With QuoteRegister To Reply
  10. 06-22-2019, 11:06 AM #10
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    Ok, I'll do that. thanks.
    Reply With QuoteRegister To Reply
  11. 06-22-2019, 11:43 AM #11
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Index/Match question

    =index(a1:a12,aggregate(15,6,row(a1:a12)/isnumber(search(text(a1:a12,"000,"),c1&",")),1))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 06-22-2019, 12:11 PM #12
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    Thanks, tim201110. but it didn't work in my example. can you have a look at it ? Please.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 06-22-2019, 12:14 PM #13
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    I get #NUM! error.
    Reply With QuoteRegister To Reply
  14. 06-22-2019, 03:11 PM #14
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Index/Match question

    it means you have more modern version than 2007
    The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.
    Reply With QuoteRegister To Reply
  15. 06-22-2019, 03:45 PM #15
    max333
    max333 is offline
    Registered User
    Join Date
    08-11-2017
    Location
    Budapest
    MS-Off Ver
    2016
    Posts
    68

    Re: Index/Match question

    yes, i have office 2016. and yes my data is horizontal. In the question I gave $C4:$K4 range.
    Reply With QuoteRegister To Reply
  16. 06-27-2019, 10:19 AM #16
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Index/Match question

    If I understand what the desired outcome should be then perhaps the following will help.
    For F2: =IF(AND(ISNUMBER(MATCH(VALUE(MID(F1,1,1)),A2:D2,0)),ISNUMBER(MATCH(VALUE(MID(F1,3,1)),A2:D2,0))),F1)
    For G2: =IF(AND(ISNUMBER(MATCH(VALUE(MID(G1,1,1)),A2:D2,0)),ISNUMBER(MATCH(VALUE(MID(G1,3,1)),A2:D2,0)),ISNUMBER(MATCH(VALUE(MID(G1,5,1)),A2:D2,0))),G1)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index / Match Question
    By xcracer41 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2018, 05:41 PM
  2. INDEX / MATCH question
    By Ray3213 in forum Excel General
    Replies: 1
    Last Post: 05-23-2017, 12:19 AM
  3. Index Match Question
    By kevbar13 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-09-2017, 10:54 AM
  4. [SOLVED] Quick INDEX MATCH MATCH OFFSET question
    By franb123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2017, 02:56 AM
  5. Index, match, match Question!. Seems so simple but can't figure it out...
    By huikimhuikim in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2013, 01:57 PM
  6. Index/Match question
    By mikera in forum Excel General
    Replies: 2
    Last Post: 10-22-2010, 02:03 PM
  7. Index/match question.
    By Jules in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-06-2006, 01:50 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1