Can VLookup return more than one look up value

[Z941714]

Good Day All,

The question is there a way that Vlookup can return more than one lookup value from the array table. The lookup value has a number of matching values in the array table.

As always thanks in advance

Z941714

 

[63falcondude]

Sure, see below.

How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

 

[wideboydixon]

You're going to need to use an array formula with INDEX/MATCH in order to retrieve subsequent matches of the same value. For example:

Book1
	A	B	C	D	E
1	Lookup	Values		ID	Text
2	1001	A		1001	A
3		D		1002	B
4		G		1003	C
5				1001	D
6				1004	E
7				1005	F
8				1001	G
9				1002	H
10				1004	I
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(INDEX($E$1:$E$10,SMALL(IF($D$1:$D$10=$A$2,ROW($D$1:$D$10)),ROWS($B$2:$B2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in B2 down.

WBD

 

[Z941714]

You're going to need to use an array formula with INDEX/MATCH in order to retrieve subsequent matches of the same value. For example:

	A	B	C	D	E
1	Lookup	Values		ID	Text
2	1001	A		1001	A
3		D		1002	B
4		G		1003	C
5				1001	D
6				1004	E
7				1005	F
8				1001	G
9				1002	H
10				1004	I

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2 {=IFERROR(INDEX($E$1:$E$10,SMALL(IF($D$1:$D$10=$A$2,ROW($D$1:$D$10)),ROWS($B$2:$B2))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Copy formula in B2 down.

WBD

Thanks boy was that quick

Z941714