Vlookup a value with multiple results

[CGV]

Hi All,

I am struggling to find a formula for an excel exercise I have tried with Match and Index but not much of a luck ( maybe the formula was not set up properly).

E.g.

I have a list with name and seats , this list has multiple name against same seat. as per below:

Seat Name
1 A
2 B
3 C
4 D
5 E
6 F
1 G
3 H

I would like to allocate the giving seat against the right name as per below :


Seat Name Should be
1 ? A
2 ? B
3 ? C
1 ? G
4 ? D
3 ? H
5 ? E
6 ? F

Can you please help me?

Thanks

Carmine

[Kaper]

I'd follow human way to find it. assuming your original data is in A2:B9 (plus headers in A1:B1) and list of seats to be reassigned starts in D2, in E2 array formula:

Please Login or Register  to view this content.

and copy down
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[CGV]

Hi Kaper,

Thank you so much about this, seems that it is what I need, but when I extent the range to the entire column the formula comes with N/A even with the CTRL+SHIFT+ENTER , can you please extend the the entire column?
Thanks

Carmine

[Tony Valko]

You should avoid using entire columns as range references in certain functions. IF being one of them.

Assume your table is the range D1:E8.

Data Range

	A	B	C	D	E
1	1	A		1	A
2	2	B		2	B
3	3	C		3	C
4	1	G		4	D
5	4	D		5	E
6	3	H		6	F
7	5	E		1	G
8	6	F		3	H
9	------	------	------	------	------

This array formula** entered in B1 and copied down:

=IFERROR(INDEX(E:E,SMALL(IF(D$1:D$8=A1,ROW(D$1:D$8)),COUNTIF(A$1:A1,A1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Kaper]

The formula shall work well if properly extended (input range is used 3 times). For instance:

Please Login or Register  to view this content.

[dflak]

If you put your data in an Excel table, then you should not have to worry about extending the formula or referencing extra rows. An Excel table knows how big it is and uses column header names instead of cell references so formulas are a lot easier to read.

This article should get you started with Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

[Tony Valko]

uses column header names instead of cell references so formulas are a lot easier to read.

I guess that's a personal preference.

Personally, I prefer cell/range addresses.

So, you send me a file and I want to review the values used in this formula:

=SUM(Groups)

If it was written as:

=SUM(Sheet2!A1:A10)

I would instantly know where to look.

But no, I first have to find out where "Groups" is located!