Help please with INDEX and MATCH

[rubsley]

Hi all, I have something similar to this:

sample.JPG

And what I'm trying to do is have it so the blank cells under CODENAME become automatically populated with the correct codename from the table above.

I have a pretty clunky formula that works one column at a time, but I figure there must be a way to have it search the entire range and return the right codename.

NB I'm using Excel 2003 and wanting to avoid arrays, if possible. Sample worksheet attached.

Cheers!

[alansidman]

Is a VBA solution ok with you?

[AliGW]

No need for VBA, but you do need to set calculations to automatic (they are manual at present).

=INDEX($D$8:$D$10,SUMPRODUCT(($E$8:$I$10=D14)*(ROW($D$8:$D$10)-7)))

[rubsley]

That's brilliant! Thank you so much. Does exactly what I need it to do.

Cheers!

[AliGW]

Glad to help!

[rubsley]

A follow up question to this: supposing I look for an alias that isn't in the index - is there a way for it to return something like "FIXNAME" highlighting that I need to add it?

I tried using ISERROR but I don't I think it recognizes the above situation as an error, instead it outputs the entire CODENAME column.

[AliGW]

Because of your Excel version, I have fudged it a bit to keep the formula as short as possible:

Excel 2016 (Windows) 32 bit

	D	E	F	G	H	I
6	CODENAME	Alias 1	Alias 2	Alias 3	Alias 4	Alias 5
7	FIX
8	ROBERT_Q	Robert		Robbie	Bobby
9	STEVEN_H	Steven	Steve	Stevo
10	MICHAEL_J	Michael	Mick	Micky	Mike	Mikester
11
12
13	Alias	CODENAME
14	Bob	FIX
15	Steve	STEVEN_H
16	Mick	MICHAEL_J
17	Micky	MICHAEL_J
18	Mike	MICHAEL_J

Sheet: Sheet1

In E14 copied down:

=INDEX($D$7:$D$10,IF(SUMPRODUCT(($E$7:$I$10=D14)*(ROW($D$7:$D$10)-6))=0,1,SUMPRODUCT(($E$7:$I$10=D14)*(ROW($D$7:$D$10)-6))))

The text in D7 can be hidden by colouring it white.

[rubsley]

You're the best.