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 CalvaccaNew 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 then returns 3 entries if there are 3, 4 entries if there are 4 etc.

Thanks again for the help!

Jerry

File size:
9.1 KB
Views:
13
2. ### Peter BartholomewWell-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 CalvaccaNew 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 BartholomewWell-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.

File size:
12.2 KB
Views:
4
5. ### bosco_yipExcel Ninja

Messages:
2,147

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

File size:
12.4 KB
Views:
5
6. ### Peter BartholomewWell-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

File size:
12.5 KB
Views:
4
7. ### Jerry CalvaccaNew 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

File size:
47.1 KB
Views:
2