1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match question

Discussion in 'Ask an Excel Question' started by Jerry Calvacca, Jan 29, 2019.

  1. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    Hello,

    Thanks in advance for the help on this.

    In this example, I would L4 to return the Name in the list if all names will be entered in B:B.

    After that has been answered, the Lines in A:A give how many entries for the name found. This may also be 1. P:p then returns 3 entries if there are 3, 4 entries if there are 4 etc.

    Thanks again for the help!

    Jerry

    Attached Files:

  2. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    647
    To return the last name in a list, I used one or other of the following

    = LOOKUP("zzzz",Name)

    = INDEX(Name, ROWS(Name))

    In future the answer to the question part B will be
    = FILTER(Destination, Name=LOOKUP("ZZZZ", Name) )
    but that is not of much use at present.
  3. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    Hi Peter,

    Thanks for the 1st part, the "LOOKUP" function worked perfectly.

    I'm not quite sure I understand the reply that you made on how to have the second part work with returning a Name with multiple destinations....thanks again for the help.
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    647
    The function I showed is not yet released as part of Excel; current practice is somewhat more tedious! The book shows a possible formula, built step by step.

    = INDEX( Destination, SMALL( Records, Seq ) )
    where 'Records' refers to the formula
    = IF(Name=LOOKUP("zzzz",Name), Seq)
    and may be viewed in Name Manager.

    Attached Files:

  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,147
    upload_2019-1-31_9-22-31.png

    1] I removed Column A data which is not required

    2] The "Last name" in cell B10, I changed it to "Jim" and B10 can change to other name for testing purpose.

    And, herein my formula solution

    3] The 1st part "Last Name in List", L4 enter formula :

    =LOOKUP("zzz",B:B)

    4.1] The 2nd part "Name", O4 enter formula and copied down :

    =IF(ROWS($1:1)<=COUNTIF(B$1:B$100,L$4),L$4,"")

    4.2] The 2nd part "City", P4 enter formula and copied down :

    =IF(O4="","",INDEX(C:C,AGGREGATE(15,6,ROW($C$3:$C$10)/(B$3:B$10=L$4),ROWS($1:1))))

    Regards
    Bosco

    Attached Files:

  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    647
    Bosco

    Thanks for posting your masterclass in traditional spreadsheet construction. I suspect my approach must come as a shock to many questioners so you presenting an alternative that is closer to expectations is a good idea.

    I became fully committed to avoiding all direct references and, to the greatest extent that I can, relative references as a result of listening to a TED talk given by Dan Bricklin on the birth of the electronic spreadsheet. He stated "Of course, one could reference data the programmers' way using names but that would be 'tedious'!" I thought there should be a better reason for departing from standard programming practice than the avoidance of a bit of tedium, so continued to explore whether there are things that cannot be done in Excel with Names and Array formulas.

    To provide something closer to a back-to-back comparison, I have removed the sequence column and provided an alternative non-CSE formulation using aggregate.

    Kind Regards
    Peter

    Attached Files:

  7. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    Hi Bosco

    Thanks for the input on this it worked perfectly!
    Using the same thoughts, is there a way to use similar functionality to find a unique number in the log and return the information regardless if it's 1, 2, 3 or 4 lines etc?

    I attached the file here so you can see where i'm at...thanks for the help!

    Jerry

    Attached Files:

Share This Page