Finding the 2nd instance in a lookup

I have a list of stores with managers' names in a worksheet called Current, but in the lookup worksheet, called Mgrs, there are sometimes two instances of the store because there is an assistant manager. Here's a sample of the data:

A B C D E
Branch Surname Given Appointed Comment
HEIDELBERG SMITH John 03/07/2006 Assistant - Returned
HEIDELBERG BLOGS Joe 31/01/2007 Heidelberg
LAVERTON JONES Jim 29/05/2006 Laverton - Returned
PENRITH DOE Jane 28/03/2007 Penrith - Interim

I'm using this formula to match the managers' names:

=INDEX(Mgrs!$B:$B,MATCH($C2,Mgrs!$E:$E,0))&" "&INDEX(Mgrs!$C:$C,MATCH($C2,Mgrs!$E:$E,0))

The formula correctly returns the first instance, but this is not always the result I need, so I'm trying to create an IF statement that will match the store location in Current with the matching characters in column E of Mgrs to give me the right result, but I'm really struggling.

I would really appreciate some help, please.

Originally Posted by rylo

Hi

What do you have in Current!C2? I put in Heidelberg and it returned Joe Bloggs, Laverton for Jim Jones and Penrith for Jane Doe.


rylo

Hi, Rylo.

I've done a bit more work on it and found the same as you. Most of it works now, but I get a #N/A result if there's more data than the store location. I'm attaching a cut-down copy of my workbook.

Thanks for helping!

Originally Posted by rylo

Trish

Try

I4: =IF(INDEX(Mgrs!$G:$G,MATCH($C4&"*",Mgrs!$P:$P,0))&" "&INDEX(Mgrs!$F:$F,MATCH($C4&"*",Mgrs!$P:$P,0))=$D4,"same",INDEX(Mgrs!$G:$G,MATCH($C4&"*",Mgrs!$P:$P,0))&" "&INDEX(Mgrs!$F:$F,MATCH($C4&"*",Mgrs!$P:$P,0)))

Copy down as required.

rylo

Many thanks, Rylo. That's exactly what I need!

Trish