+ Reply to Thread
Results 1 to 8 of 8

Index match with condition from second index match

  1. #1
    Registered User
    Join Date
    11-19-2019
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    7

    Question Index match with condition from second index match

    I have two tables in my document looking like this:

    Serial Number Table (in formula : SNT):
    Please Login or Register  to view this content.
    Product Technical Specification (in formula: PTS):
    Please Login or Register  to view this content.
    and the goal is to list every serial numbers associated to a product that needs labeling and their product number and power:
    Please Login or Register  to view this content.
    So I tried to write my own Index/Match formula and it somehow wont filter the items that shouldn't be labeled. The formula looks like this:

    Please Login or Register  to view this content.
    My result is:
    Please Login or Register  to view this content.
    I might have made my match a bit overly complicated but these tables are over different sheets and I do not know any better to see if labeling is 1 or 0. And to be honest, I've been looking around for an explaination of what MATCH(0,1,0) is doing exactly and couldn't find anything. How can the range be 1 and the value looked up 0?

    Thank you for any help!=IFERROR(INDEX(SNT[Serial number];MATCH(0;COUNTIF(A$1:$A1;SNT[Serial number])+IF(INDEX(PTS[Labeling];MATCH(INDEX(SNT[Product model];MATCH(INDEX(SNT[Serial number];SNT[Serial number]);PTS[Product model];0))<>1;1;0);0));"")
    Attached Files Attached Files
    Last edited by scharpentier; 11-19-2019 at 09:14 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index match with condition from second index match

    Please read the yellow banner at the top of the page and then attach a sample sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    11-19-2019
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Index match with condition from second index match

    Since my Excel wasn't in english, I tough I had to change the language... Turns out it translates the formulas. So here it is, sample sheet added to original post, thank you.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index match with condition from second index match

    Microsoft thought of that !!

    I'm not at all clear what you EXPECT so see. Forget about the non-working formula. What do you want to see on the 3rd sheet, and why?

  5. #5
    Registered User
    Join Date
    11-19-2019
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Index match with condition from second index match

    I want a list containing the informations that needs to be output to a label. Those informations will be fed to a labeling system trough CSV. I want to be able to add to the first table in the futur and automaticaly generate the label content. The label has to contain the serial number, product model and other product technical specification.

    On the 1st sheet, the unique serial number is assigned to a specific product model .

    On the 2nd sheet, the unique product model is listed with its other specifications . One of the specification relates to wether the product is labeled or not.

    On the 3rd sheet, I EXPECT to list all serial numbers associated to a product that needs labeling, but NOT the serial numbers associated to a product that doesn't require labeling.

    I hope I'm clear, let me know if I'm not and thank you
    Last edited by scharpentier; 11-19-2019 at 09:24 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index match with condition from second index match

    Away for a while...

    Just one more Q. Will only one product at a time be labelled as 1?

  7. #7
    Registered User
    Join Date
    11-19-2019
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Index match with condition from second index match

    The truth is that all products are going to be labeled, but with different dimentions of labels, so I'll have a sheet for each size of labels, where 1 is the first size and could be replaced with "120mm x 300mm". There will be many products sharing the same label size. I tried to simplify it to eighter 1 or 0 with only 2 products but the list of products could also change in the futur.

    I feel like the problem with the formula is that finding the products labeling property shouldn't be an array formula, only the outer index needs to be an array formula. What I might do is a step in between with a sheet listing all serial numbers and all products model with their labeling and technical specifications and then filter that one list containing all the content of every labels by label size. It's an extra sheet but I wouldn't mind and that might fix the non array formula inside array formula problem, by splitting the listing from the filtering.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index match with condition from second index match

    I'm not convinced that I understand what's needed yet.... confused by your last reply. But for starters:

    A2:
    =IFERROR(INDEX(INDEX(PTS[Product model],MATCH(1,PTS[Labeling],0)),MATCH(0,INDEX(--(COUNTIF($A$1:A1,INDEX(PTS[Product model],MATCH(1,PTS[Labeling],0)))=COUNTIF(SNT!$A$2:$A$5,INDEX(PTS[Product model],MATCH(1,PTS[Labeling],0)))),0),0)),"")

    B2:
    =IFERROR(INDEX(SNT!B:B,AGGREGATE(15,6,ROW(SNT[Serial number])/(SNT[Product model]=A2),COUNTIF(Labeling!$A$2:A2,Labeling!A2))),"")

    C2:
    =IFERROR(VLOOKUP(A2,PTS!A:B,2,FALSE),"")

+ 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] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  2. Index match with condition
    By felixpanganiban in forum Excel General
    Replies: 2
    Last Post: 11-29-2015, 04:22 AM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  5. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. Using INDEX, MATCH, but need condition
    By philiphales in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-25-2005, 10:27 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