excel - use index match to find result from multiple columns and rows

Question

I am wanting to use excel index match function to find a result by searching multiple columns and rows.

I have the following table set-up:

Depot    Manger     Employee    Employee
1        Dave       Haris       Alex
2        Linda      Elliot      Tom
3        Eric       Michael     Lucy
4        James      Glenda      Mark
5        Frank      John        Paul

I want to get the depot number for the relevant employee or manager where the manager name or employee name matches the value in my cell AE16.

So let's say I type the employee Alex into my cell AE16 then my cell containing my formula should come back with depot 1.

At the moment I am using the following but it can only search one column not multiple columns. Could someone please show me how I can do this? Thanks in advance.

=INDEX('Depot Data'!A4:A509,MATCH(AE16,'Depot Data'!B4:BH1644,0))

Which Depot shall be found if a Manager and a Employee have the same name? — Axel Richter
The Employee and Manager will never have the same name. I have used only first names in this instance but on my actual spread sheet I am using first and last name — james tanner

Axel Richter Axel Richter · Accepted Answer · 2014-09-19T11:25:33

The conventional way:

=INDEX('Depot Data'!$A$4:$A$10,
IFERROR(MATCH(AE16,'Depot Data'!$B$4:$B$10,0),
IFERROR(MATCH(AE16,'Depot Data'!$C$4:$C$10,0),MATCH(AE16,'Depot Data'!$D$4:$D$10,0))))

I have it only up to row 10. But this you should able to correct easily.

Or with a array formula:

{=INDEX('Depot Data'!$A$4:$A$10,
MIN(IF('Depot Data'!$B$4:$D$10=AE16,ROW('Depot Data'!$B$4:$D$10)-3,11-3)))}

You know, how to handle such array formulas?

The -3 part must be changed to ([firstRow]-1), if it not starts in row 4. And the 11-3 part must be changed to [lastRow]+1-([firstRow]-1).

How from XOR LX mentioned in the comments, the following array formula is more flexible, because the start and length parts changes automatically if one inserts or deletes rows in 'Depot Data'!Row1:10. But of course it is more complex and harder to understand for beginners.

{=INDEX('Depot Data'!$A$4:$A$10,
MIN(IF('Depot Data'!$B$4:$D$10=AE16,
ROW('Depot Data'!$B$4:$D$10)-ROW('Depot Data'!$B$4)+1,
ROWS('Depot Data'!$B$4:$D$10)+1)))}

Greetings

Axel

excel - use index match to find result from multiple columns and rows

1 Answers