Multiple results from lookup

[matbrown217]

Hi,

I would like to return multiple values from a vlookup is this possible?

I am using excel 2007.


Many Thanks

Mat

 

[Akashwani]

Hi Mat,

Welcome to MrExcel.

Is this the type of Lookup you want to do?

Excel Workbook
	A	B	C	D
1	CustomerID	Data1	Data2	Data3
2	AB123	Cat	Plane	100
3	Ab124	Dog	bus	125
4	AB123	Fish	car	150
5	AB125	Panda	boat	100
6	AB123	Cow	bike	250
7	Ab125	Bull	trike	125
8	ab123	Rabbit	Tut Tut	100
9	AB124	bird	Van	250
Table

Excel Workbook
	A	B	C	D	E
1	*	CustomerID	Data1	Data2	Data3
2	4	AB123	Cat	Plane	100
3	*	*	Fish	car	150
4	*	*	Cow	bike	250
5	*	*	Rabbit	Tut Tut	100
Lookup

You could also use a Pivot Table....

Excel Workbook
	F	G	H	I	J
12	*	*	*	*	*
13	*	CustomerID	AB123	*	*
14	*	*	*	*	*
15	*		*	*	*
16	*	Data1	Data2	Total	*
17	*	Cat	Plane	100	*
18	*	Cow	bike	250	*
19	*	Fish	car	150	*
20	*	Rabbit	Tut Tut	100	*
21	*	*	*	*	*
Lookup

See if this file helps you out.....

http://www.4shared.com/document/6KqPBIDE/Lookup_Multiple_Returns.html

Good luck.

Ak

 

[T. Valko]

Hi,

I would like to return multiple values from a vlookup is this possible?

I am using excel 2007.


Many Thanks

Mat

There is an example here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[pboltonchina]

Yes Select the number of cells you want results in, in my case 3, enter this formula and confirm it by pressing Ctrl+Enter

=VLOOKUP(A1,A1:D13,{2,3,4},0)

The above formula looks for the value of A1 in the range of A1 to D13 and returns the results from B1, C1 & D1 in the range of 3 cells you selected. Change ranges etc to suit.

 

[matbrown217]

Hi,

I have the following scenario starting in A1

Sheet 1
A B
1 Part All Shortages
2 wheel
3 engine
4 gearbox
5 lights

Sheet 2
A B
1 Part Shortage
2 wheel tyre
3 wheel alloy
4 wheel dustcap
5 engine sparkplug
6 engine valve
7 gearbox sprocket
8 gearbox clutch plate
9 lights Bulb
10 lights glass

I would like to be able to lookup the part and return all of the shortages associated with the part into sheet 1 into the relevant cell.

Many Thanks

 

[Haseeb Avarakkan]

Hi, Try

On Sheet1 B2, copy across & down.

=IFERROR(INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A$2:$A$100=$A2,ROW(Sheet2!$B$2:$B$100)),COLUMNS($B2:B2))),"")

Confirmed with Contrl+Shift+Enter, not just Enter.

 

[Akashwani]

Hi Mat,

This may not be the best solution, but it's all I can think of based on the information you have provided and my limited skills....

Excel Workbook
	A	B	C	D	E	F
1	Count	Part All Shortages	Part1	Part2	Part3	Part4
2	3	wheel	tyre	alloy	dustcap	*
3	2	engine	sparkplug	valve	*	*
4	2	gearbox	sprocket	clutch plate	*	*
5	2	lights	Bulb	glass	*	*
6	*	*	*	*	*	*
Sheet1

Excel Workbook
	A	B	C
1	Part Shortage	Extract1	Extract2
2	wheel tyre	wheel	tyre
3	wheel alloy	wheel	alloy
4	wheel dustcap	wheel	dustcap
5	engine sparkplug	engine	sparkplug
6	engine valve	engine	valve
7	gearbox sprocket	gearbox	sprocket
8	gearbox clutch plate	gearbox	clutch plate
9	lights Bulb	lights	Bulb
10	lights glass	lights	glass
Sheet2

I hope that works or gives someone a better idea/solution for you.

Ak

 

[T. Valko]

Hi,

I have the following scenario starting in A1

Sheet 1
A B
1 Part All Shortages
2 wheel
3 engine
4 gearbox
5 lights

Sheet 2
A B
1 Part Shortage
2 wheel tyre
3 wheel alloy
4 wheel dustcap
5 engine sparkplug
6 engine valve
7 gearbox sprocket
8 gearbox clutch plate
9 lights Bulb
10 lights glass

I would like to be able to lookup the part and return all of the shortages associated with the part into sheet 1 into the relevant cell.

Many Thanks

Will the data on Sheet2 be sorted or grouped together as is shown in your sample data?