Need help using Index/Match instead of Vlookup

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
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:

Code:
=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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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:

Code:
=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
ABCDEF
1
2 
3Manager1200102001120012
4Manager23001030011
5Manager340010400114001240013
6Manager450010
7
8
9
10
11
1230011Manager2
1367345 
1440011Manager3
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

Board Regular
Joined
Dec 23, 2004
Messages
212
I was close this time wasn't I Aladin? Man I'm getting good. You da man Aladin!
 

Forum statistics

Threads
1,136,878
Messages
5,678,312
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top