Continue Searching a table with Vlookup despite finding one match

Im using vlookup to search a table and fill out a calendar with dates that various employees are gone. They can have multiple trips in one month and thus will be in the table multiple times. I have the calendar set up so days go across the top and names go along hte left side. so each cell is identified by name and day. I then have each cell using the vlookup command to search the table. now if one person has multiple trips a month is there a way to compare multiple entries in the table?

Hi

1) Is a person likely to have more than 1 trip in a day?
2) What do you want to appear in the intersect cell of the name / date? A number, a "y" or something else.

Also, can you attach an example file of your situation so we can tailor a response to your structure.

rylo

Answers to your question
1) A person can only have one trip a day, but multiple trips a month/year.

2) I have the region showing in the cell if a person is on a trip then. Then conditional formatting changes the font and background to the same colors to match the key.

I cant make a sample sheet, i tried just copying small parts of my sheet but it wasnt working. So here is the formula from the cells

Please Login or Register  to view this content.

Its really nasty. if you want i can email the entire thing to you, but the zipped file is massive and i cant connect it to this forum

thanks

So,
ive done a bit of work and made the formula more simple. Which has made the file much smaller. Here it is

Hey does anyoen know if there is a vlookup similiar function that will look using multiple reference, say a name and date. In my example Name and Begin Day is used to find region.
Thanks

I can not follow your excel file to see what you are trying to do...

But to do a vlookup with multiple columns, use the concatenate function then vlookup on the resulting data

Hi

Can you pick a couple of Names, on that does match and one that doesn't and explain what should happen for both these items. Give us the actual output, and the why - don't try to give formulas.

rylo

So heres a little explaination of my excel sheet.

For Ives, Ed if you go to the background you see there are two trips scheduled for him. one from 7/2 to 7/4 and another from 7/28 to 8/3. The way it should work is that both trips would show up on the Calendar sheet. However, right now only one trip is showing up on the calendar. It is pretty simple to deduce why though, the vlookup function uses a serial search to find your reference in the table. Once it is found it exits the search.

So what im trying to do is to continue searching if the date found with the name does not fit the date on the calendar.

Hope that helps
Thanks
Dlroo

psumvp
ive tried working out the concatenate function you mentioned, but in the process I realized that it probably wouldnt work without alot of extra work and space. The trips i have in my table are denoted with an end day and a begin day so to use the & function i would have to do a seperate column on the table for each day of the trip. its a great idea and i can see how it would work but its not the right idea for my situation. thank you for it though.

dlroo

This only a start, but may get you going.

1) C6: =SUMPRODUCT(--(Background!$A$2:$A$7=Calendar!$A6),--(Background!$F$2:$F$7=Calendar!$B6),--(Background!$G$2:$G$7<=DATEVALUE(C$5&$I$2&$C$2)),--(Background!$H$2:$H$7>=DATEVALUE(C$5&$I$2&$C$2)))
2) Put your cursor in C6, then format, conditional formatting, cell value is: greater than or equal to: 1, then enter your format.
3) copy down across as required.

If this is giving the right information, then you could turn off the cell formatting and perhaps use an event macro to color those cells that were non zero with the color scheme that is relevant for the job/region.

rylo

Rylo, I dont really follow what you have there, is it zero wehn the date doesnt show and non zero when the date is in the list?
Thanks
Dlroo

Would using Index work? I dont really understand the function, does it return a value using multiple references?

So I should create another sheet with multiple tables. Each table refers to one person and contains all there trips. and then in the calendar sheet i lookup the people with regards to their respective trips. that will probably work. Ill work on it and get back to you

I was just gonna make one sheet so that it was cleaner.

oops correction ,

there should be sufficient room on look up sheet for each person up to 500 rows

should say

"sufficient room on background sheet for each person 's appointments up to 500 rows"

Martin,
So i have the individual persons table set up. How do i fill the calendar now. i cant really use a vlookup because if the date falls inbetween the begin and end date it wont register in the vlookup.
Again,
Thanks for the help

unfortunately i havent worked that out!yet
you have the start and end dates
so it should be able to populate/shade cells based on that info
might have to add a flag so it only goes from one start to next end else it will shade all cells between any date.
ill give it some thought

any one else know how this could be done?

What i had been doing was using vlookup to find the person in the background sheet and then checking to see if the date fell in between the range of dates. the only problem with that was that it would only populate the first trip. now the problem is i do not know what to reference if i were to use a vlookup. If i do a vlookup with a range (approximate) it would register a trip when there wasnt one.

ok ive pulled start and end into calendar using match(see tab)
now you need to format cells so it colours in between those numbers taking into account americas,europe ect, i think this can be done in excel 2003 or higher.
i pulled ed ives name from o61
this is getting a tad harder as you'd have to repeat formula for each name
and 2 you have to account for when appointment runs accros a month in the formatting

The problem with the spanning months is why i was using a vlookup and boolean >= start and <= end but then the problem arose with only one entry per person.

i supose you could cheat and put whole year on a sheet
then create 12 monthly sheets referencing the year sheet
with a bit of vba to copy formats