VLookup return multiple values

[OSUpokesgirl72]

Hi, I know there are many threads that exist for VLookup returning multiple values - I am having a difficult time applying it to my spreadsheet, especially with existing formulas.

I am trying to create a project grid of "flowers" and what "tasks" are to occur on what dates. In some instances, there are multiple tasks to be completed. Of course, if a task is not present on a date, I would like the grid to remain blank.

Currently I have a VLookup set within an IF function, so the flowers project grid returns tasks or blanks. However I am lacking the additional tasks occuring on a date.
I hope that someone may be able to help me with this issue. Thanks in advance!

(I had a hard time uploading an example image of my spreadsheets, therefore saved as an image in Facebook below.)
https://www.facebook.com/media/set/?set=a.10100101894419732.2484103.17101327&l=3dc9287db5

 

[Akashwani]

Hi,

Welcome to MrExcel.

Take a look here and see if they help...

http://www.mrexcel.com/forum/showthread.php?p=2719723#post2719723



You may want to post some sample data using....
http://www.mrexcel.com/forum/showthread.php?t=399380

Good luck

Ak

 

[OSUpokesgirl72]

Hi, thanks for the reply!

I'll have to wait until I get home on my own PC to try the addition of data through the links. (on my work PC - can barely even get on this site!) For the meantime, I just took a screenshot and had saved on the Facebook link.

Hate to be a pest, I am having such a difficult time trying to read through the other examples and mold to fit my own situation! I'm relatively new to Excel and can barely Vlookup to save my life

 

[Akashwani]

Hi,

In the absence of some sample data I have put this together for you.
Is this the type of situation you have?

Excel Workbook
	A	B	C	D
1	CustomerID	Date	Data2	Data3
2	AB123	05/05/2011	Plane	100
3	Ab124	15/05/2011	bus	125
4	AB123	05/05/2011	car	150
5	AB125	25/05/2011	boat	100
6	AB123	15/05/2011	bike	250
7	Ab125	15/05/2011	trike	125
8	ab123	25/05/2011	Tut Tut	100
9	AB124	05/05/2011	Van	250
Table

Excel Workbook
	A	B	C	D	E
1	*	Date	CustomerID	Data2	Data3
2	3	05/05/2011	AB123	Plane	100
3	*	*	AB123	car	150
4	*	*	AB124	Van	250
Lookup

The formulas in C2, D2, E2 need to be entered with ctrl shift enter NOT enter.
You can then copy them down as far as is required. You will obviously have to change the Sheet names and cell references to suit your data.

I hope that helps you.

Ak

 

[OSUpokesgirl72]

Thanks for your patience.

Let me try this and see if it may make sense as some sample data.

Sheet1
A B C D
1 BLANK 5/15/11 5/22/11 5/29/11
2 Roses Planting
3 Peonies BLANK Planting
4 Hibiscus BLANK Weeding Trimming

Formula currently used to return data in b2:d4 (green values)
=IF(ISNA(VLOOKUP(B$1&$A2,Sheet2!$C:$D,2,0))," ",(VLOOKUP(B$1&$A2,Sheet2!$C:$D,2,0)))


Sheet2
A B C D
1 Date Plant Date/Plant Activity
2 5/15/2011 Roses 40678Roses Planting
3 5/22/2011 Peonies 40685Peonies Planting
4 5/29/2011 Hibiscus 40692Hibiscus Trimming
5 5/15/2011 Roses 40678Roses Trimming
6 5/22/2011 Hibiscus 40685Hibiscus Weeding

I would like to return all activities to the grid in Sheet1 from Sheet2 d2:d6 that correspond to values in c2:c6. My current formula is successful in returning a single activity that corresponds to both the date and plant type and supplies a blank cell for NA values. I'm not sure on how to rewrite the formula to incorporate my original formula and your suggestions on vlookup with multiple values returned.

Again, thank you so much for your help. Not only is this a great challenge for me, it's also an adventure!

 

[Akashwani]

Hi,

So sheet2 looks like this, does it?...

Excel Workbook
	A	B	C	D
1	Date	Plant	Date/Plant	Activity
2	15/05/2011	Roses	15/05/11 Roses	Planting
3	22/05/2011	Peonies	22/05/11 Peonies	Planting
4	29/05/2011	Hibiscus	29/05/11 Hibiscus	Trimming
5	15/05/2011	Roses	15/05/11 Roses	Trimming
6	22/05/2011	Hibiscus	22/05/11 Hibiscus	Weeding
Sheet2

Does Sheet1 have this format before the results?....

Excel Workbook
	A	B	C	D
1	*	15/05/2011	22/05/2011	29/05/2011
2	Roses	*	*	*
3	Peonies	*	*	*
4	Hibiscus	*	*	*
Sheet1

I have no idea on how you can achieve your goal IF you have a duplicate like 15/05/11 Roses Planting and 15/05/11 Roses Trimming.

If you didn't have duplicates then an Index/Match/Match formula may be the way to go or rearrange your data like this and use a Vlookup.....

Excel Workbook
	A	B	C	D
1	Date/Plant	Date	Plant	Activity
2	15/05/11 Roses	15/05/2011	Roses	Planting
3	22/05/11 Peonies	22/05/2011	Peonies	Planting
4	29/05/11 Hibiscus	29/05/2011	Hibiscus	Trimming
5	22/05/11 Roses	22/05/2011	Roses	Trimming
6	22/05/11 Hibiscus	22/05/2011	Hibiscus	Weeding
Sheet2

Excel Workbook
	A	B	C	D
1	*	15/05/2011	22/05/2011	29/05/2011
2	Roses	Planting	Trimming	*
3	Peonies	*	Planting	*
4	Hibiscus	*	Weeding	Trimming
Sheet1

The formula in B2 can be copied across and down as required.

I'm sorry if that isn't the solution you require.

Ak

 

[OSUpokesgirl72]

Thanks so much for your help.

I'm going to try reformatting the data to see if one of your suggestions may solve the problem. Perhaps I just need to look at my desired end result a little differently too.

 

[Akashwani]

Hi,

Just one last thought on this, have you tried a Pivot Table?
You can arrange the data to suit, Filter by Date or Filter by Activity.
This will be able to handle your duplicates and you can place the Pivot Table on another sheet.

Ak