How to return multiple texts on same cell when using Vlookup

[aubbus]

Hi,
I have an excel sheet with this info
A B
Cake 10
Bread 5
Coffee 5
Chocolate 8
Cheese 5
.... big list

I want to pull up all the text into one cell or adjacent cells which has the same price.

For Eg., lets say I choose 5 (5 is automatic update and is available on cell)
the formula for the current cell to look up 5 and enter all the text in adjacent cells one by one below

Current Cell e10
Bread
Next cell e11
Coffee
Next cell E12
Cheese

Something like that..

 

[Akashwani]

Hi Welcome to MrExcel.

This is sample data to give you an idea....

Excel Workbook
	A	B	C	D	E	F
1	ID Number	Name	Amount	Animal	Transport	Date
2	AA10000	tom	368.00	cat	Bike	01/01/2011
3	AA10001	****	277.00	dog	Car	02/01/2011
4	AA10002	Harry	474.00	Horse	Bus	03/01/2011
5	AA10003	Sally	163.00	Mouse	train	04/01/2011
6	AA10004	Mary	330.00	fish	Tram	05/01/2011
7	AA10005	Jim	346.00	cat	Plane	01/01/2011
8	AA10006	Jill	472.00	Panda	Tut Tut	07/01/2011
9	AA10007	Jo	343.00	Tiger	Coach	08/01/2011
10	AA10008	Joe	300.00	Lion	Carriage	09/01/2011
11	AA10009	Tim	343.00	Tiger	Bus	10/01/2011
12	AA10010	Stan	100.00	cow	trike	01/01/2011
13	AA10011	tom	301.00	cat	Bike	12/01/2011
14	AA10012	Dom	147.00	dog	Car	01/01/2011
15	AA10013	Harry	267.00	Horse	Bus	04/01/2011
16	AA10014	Sally	458.00	Mouse	train	05/01/2011
17	AA10015	Mary	436.00	Horse	Tram	06/01/2011
18	AA10016	Jim	299.00	Mouse	Plane	02/01/2011
19	AA10017	Homer	455.00	fish	Tut Tut	09/01/2011
20	AA10018	John	399.00	pig	Coach	01/01/2011
Master

Excel Workbook
	A	B	C	D
1	5	Name	*	Amounts
2	*	Jim	*	346.00
3	*	*	*	299.00
4	*	*	*	399.00
5	*	*	*	421.00
6	*	*	*	421.00
Multiples

The formula in D2 needs entering with ctrl shift enter NOT enter.
You can change the range of cells to suit yours.

I hope that helps.

Good luck.

Ak

 

[aubbus]

HI,
Thanks for the fast reply. Do you think this would work with text as well. Because I am looking to return text per value and my search column(text) is to the left of value.

 

[aubbus]

I guess that problem is only for VLookup.. Let me try this.. Thanks for the input.

 

[aubbus]

Hi Akashwani,
i follow your logic until here..
Master!D$2:D$43)),ROWS(D$2:D6))-ROW(Master!$A$2)+1),"")
What does master D2..D43 does?
What does master$a$2 does? and why no spaces""
and why add 1?..

I would be great if you could explain the logic.. Sorry I know i am asking more, but i am a beginner trying to learn.

When I used the formula i get an error saying inconsistent formula..

 

[Peter_SSs]

I'm wondering what you want to with the values afterwards, if anything? Is this just so you can see them all together? If so, cosider trying AutoFilter on the columns and AutoFilter column B to '5', for example.

 

[Akashwani]

Hi aubbus,

It's probably best if I let Mike explain it to you....

http://www.youtube.com/user/ExcelIsFun#p/search/14/6bGKhbUYOas

http://www.youtube.com/user/ExcelIsFun#p/search/67/8HNeXqaOsY0

Ak

 

[aubbus]

Thanks