VLOOKUP help please.

[steiny0831]

Hi there,

I am working on a scheduling spreadsheet for our business. Not sure if what I want to do is possible with vlookup or not or if there is another formula that can do what I am trying to do.

For each day I have 4 slots for a class and time. Meaning some days we will have 1 class and others we could have up to 4.
When I write my vlookup formula it works kind of but did not account for it some situations.
I need the 3 columns to only show the classes on the specific rows for the days, show blanks if there are not enough to fill all for slots. And show different classes if there are more than 1 class for a day.
Right now its only showing the 1st class of the day and filling all 4 slots with the 1st class only.

I have attached a link to see what it is I am needing. Shows my data its pulling from, shows what my vlookup pulls back, and then shows how I need to show.


https://docs.google.com/spreadsheets/d/1DJp-er_XeX1ONFQMsO7JzO-n8B1WkqQKLMuE1S3hAG8/edit?usp=sharing

Thank you for any help anyone can provide.
Jeff

 

[TimNevins]

VLOOKUP isn't what you need here. VLOOKUP finds one unique value and doesn't have the capacity for "bring me values for all instances of the search value". INDEX, MATCH is what you need, I think. There are many examples of index, match in the forum. I think the only thing that might cause you problems is if you have multiple instances of the same class on the same day. Your sample doesn't show that, but you never know.

Good luck!

 

[steiny0831]

Ah, Okay.

I will take a look at the index and match formulas and see what might work best for this.

Thanks for taking a look.
Jeff

 

[AhoyNC]

See if this works.

NOTE: formula in cell F3 is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag down column as needed.

Excel Workbook
	A	B	C	D	E	F	G
2	Day	Class	Time		Day	Class	Time
3	1	Wicked Colorado Winter	6:30pm - 8:30pm		1	Wicked Colorado Winter	6:30pm - 8:30pm
4	2	Rustic Highland Cow Cutout	6:30pm - 8:30pm		1
5	2	Ethereal Blossom	6:30pm - 8:30pm		1
6	3	Pastel Elephants	6:30pm - 9:30pm		1
7	4	Family Class, Starry Night for Kids ($25)	10:00am - 11:30am		2	Rustic Highland Cow Cutout	6:30pm - 8:30pm
8	4	Bottomless Mimosas! Sunshine Daydreams	1:00pm - 3:00pm		2	Ethereal Blossom	6:30pm - 8:30pm
9	5	Mystic Forest Meeting Set	6:30pm - 8:30pm		2
10	5	Twisted Sunrise	7:00pm - 8:30pm		2
11	6	Anchor of Love	6:30pm - 8:30pm		3	Pastel Elephants	6:30pm - 9:30pm
12	6	Carried me through	6:30pm - 8:30pm		3
13	7	Sunflowers 10 x 30	7:00pm - 9:00pm		3
14	8	*Wood Art* Wild and Free	6:30pm - 8:30pm		3
15	9	To the Moon and Back III	6:30pm - 8:30pm		4	Family Class, Starry Night for Kids ($25)	10:00am - 11:30am
16	10	Rustic Colorado Set -Couples or Singles	6:30pm - 8:30pm		4	Bottomless Mimosas! Sunshine Daydreams	1:00pm - 3:00pm
17	11	Family Class! Scribbled Heart ($25)	2:00pm - 3:30pm		4
18	11	Graffiti Aspen Sunset Set	7:00pm - 9:00pm		4
19	13	Anchored Love- Couples Set ($30pp)	6:30pm - 8:30pm		5	Mystic Forest Meeting Set	6:30pm - 8:30pm
20	13	You Make Me Happy	6:30pm - 8:30pm		5	Twisted Sunrise	7:00pm - 8:30pm
21	14	Love Ripples Set	6:30pm - 8:30pm		5
22	14	Deer at Sunset Set (Couples or Singles)	6:30pm - 8:30pm		5
23	16	Snowy Bridge Set	6:30pm - 8:30pm		6	Anchor of Love	6:30pm - 8:30pm
24	16	Babe-licious	7:00pm - 9:00pm		6	Carried me through	6:30pm - 8:30pm
25	17	** Wood Art** Welcome to our Home	2:00pm - 4:00pm		6
26	17	Harvest Moon Light	6:30pm - 8:30pm		6
27	18	Winter Wonderland	6:30pm - 8:30pm
28	20	Happy Sloth	6:30pm - 8:30pm
29	20	Morning Splendor	6:30pm - 8:30pm
30	21	By Request! Colorado Balloon Ride	6:30pm - 8:30pm
31	21	Alaskan Night	6:30pm - 8:30pm
32	22	Paint for a Purpose: North High Alumni Assoc.	6:30pm - 8:30pm
33	23	*NEW* Wicked Colorado Winter Moon	6:30pm - 8:30pm
34	24	Pristine Peacock 10 x 30	2:00pm - 4:00pm
35	27	Off the Edge	6:30pm - 8:30pm
Sheet

 

[steiny0831]

AH, Yes, this it perfectly. Thank you very much!