Index Match question

[Jerry Calvacca]

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

 

[Peter Bartholomew]

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.

 

[Jerry Calvacca]

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.

 

[Peter Bartholomew]

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.

 

[bosco_yip]

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

 

[Peter Bartholomew]

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

 

[Jerry Calvacca]

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