Need help using Index/Match instead of Vlookup

[rrmando]

Hello everyone. Hope all is well. I am trying to use Index/Match but am not sure if I might be doing it backwards. We cannot use Vlookup. I looked at nested IF statements, but I don't believe it could handle a long list as ours.

I have a list of managers and the branch offices they are responsible for. I.e.:

• Manager1 - 20010, 20011, 20012
  Manager 2 - 30010, 30011
  Manager3 - 40010, 40011, 40012, 40013
  Manager4 - 50010

The actual list is of managers and offices is longer than this one. As you can see not all managers have the same number of branch offices. I need to find the Manager name based on the branch office entered. Does Index/Match require that all cells in the the index range contain a value? I keep getting an #N/A error. This is the formula I'm attempting to use:

=INDEX(A16:F25,MATCH(D13,A16:A25,0),1)

• D13 = branch office entered
  Column A = Manager Name
  Columns B, C, D, E, and F = branch offices

Can someone please point me in the right direction? Thank you.

 

[Aladin Akyurek]

Hello everyone. Hope all is well. I am trying to use Index/Match but am not sure if I might be doing it backwards. We cannot use Vlookup. I looked at nested IF statements, but I don't believe it could handle a long list as ours.

I have a list of managers and the branch offices they are responsible for. I.e.:

• Manager1 - 20010, 20011, 20012
  Manager 2 - 30010, 30011
  Manager3 - 40010, 40011, 40012, 40013
  Manager4 - 50010

The actual list is of managers and offices is longer than this one. As you can see not all managers have the same number of branch offices. I need to find the Manager name based on the branch office entered. Does Index/Match require that all cells in the the index range contain a value? I keep getting an #N/A error. This is the formula I'm attempting to use:

=INDEX(A16:F25,MATCH(D13,A16:A25,0),1)

• D13 = branch office entered
  Column A = Manager Name
  Columns B, C, D, E, and F = branch offices

Can someone please point me in the right direction? Thank you.

Book3
	A	B	C	D	E	F
1
2
3	Manager1	20010	20011	20012
4	Manager2	30010	30011
5	Manager3	40010	40011	40012	40013
6	Manager4	50010
7
8
9
10
11
12	30011	Manager2
13	67345
14	40011	Manager3
15
Sheet1

B12:

=INDEX($A$2:$A$6,MIN(IF($B$2:$E$6=A12,ROW($B$2:$E$6)-ROW($B$2)+1)))

which is confirmed with control+shift+enter, not just with enter, and copied down.

Note the additional record at the beginning with A2 housing:

=""

 

[rrmando]

I was close this time wasn't I Aladin? Man I'm getting good. You da man Aladin!