Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Vlookup problem

Posted on 2014-02-23
13
Medium Priority
?
470 Views
Last Modified: 2014-02-24
I have created a two-worksheet Excel spreadsheet.  The first worksheet, Master Schedule, is a complicated looking affair where I layout my schedule of posting blogs.  The columns include among others:
Day
Time for the posting to occur
Platform
Blog #
Plus 17 columns for the various times of day I plan on scheduling posts

Master Schedule worksheet
So one row might indicate that on Monday, at 9:30 AM, a Twitter post will be made for my first blog of that week.  Across that row, in the column for 9:30 AM, a 1 will appear in that row.

The second worksheet is where I want to create a more visual, more easily understood grid for the schedule.

The first column shows 7 rows labeled for the days of the week.  Across the top are columns labeled for the platform (LinkedIn, Twitter, Facebook) and the time for the posting.

Visual Schedule worksheet
In my approach, I only post to LinkedIn at 7:00, 8:00, 18:00 and 22:00.  Likewise, I only post to Twitter at 7:30, 9:30, 15:30, 17:30 and 19:30.

I'm using a Vlookup formula.  Below are a few examples.

For Monday at 7:00 am, =VLOOKUP($A3,'Master Schedule'!$C$4:$T$45,5,FALSE)
For Monday at 7:30 am, =VLOOKUP($A3,'Master Schedule'!$C$4:$T$45,6,FALSE)

The problem is that my Vlookup is only working in showing the first value it finds for a posting for that day.  Thus, where Monday should reflect two postings, it only shows the first one at 9:30 AM.  Thursday should also show two, but it only shows the first one at 18:00.  Saturday should show four, but it only shows the first one at 8:45.

What approach do I need to use to pick up all the matches?  Is the answer in the function or formula or is it in how I populate the "Master Schedule" sheet.

BTW, I use the "Master Schedule" to actually create the schedule.  I want the "Visual Schedule" sheet to make it easy to see what the schedule looks like.

Thanks.
Social-Networking-Schedule.xlsx
0
Comment
Question by:coachjim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39880790
i have one trick to overcome this kind of  situation....i suggest create one column in  master schedule which combine Day & Time &  Platform Used in column A  i.e. =D4&TEXT(E4,"HH:MM")&F4
Day Time Platform Combine
so you will get your all look up values perfect,Values

if you want like this then i will further help you with formula. i had enter only one cell formula in attached with yellow background.

let me know

Thanks
Social-Networking-Schedule.xlsx
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 39881046
You could use the countifs function to combine the different criteria.

=COUNTIFS('Master Schedule'!$A$4:$A$45,B$1,'Master Schedule'!$C$4:$C$45,$A3,'Master Schedule'!$D$4:$D$45,B$2,'Master Schedule'!$U$4:$U$45,">0")

Open in new window


Then you will get the number posted for Platform, Day, Time, Made (column U).
The number will be the total for all weeks.
Social-Networking-Schedule-1.xlsx
0
 
LVL 2

Author Comment

by:coachjim
ID: 39881339
ITJockey:

I have recreated your formula and locked in the row number so it looks like this:

=VLOOKUP($A4&TEXT(L$2,"HH:MM")&L$1,'Master Schedule'!$A$4:$U$45,MATCH('Visual Schedule'!L$2,'Master Schedule'!$A$3:$U$3,0),FALSE)

That works in any cell where it should.  Everyplace else it returns a #N/A.

What should be added to avoid that?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Author Comment

by:coachjim
ID: 39881340
hgholt:

Thanks for you're suggestion.  My wife is calling me away right now.  I'll look at that later.

Jim
0
 
LVL 2

Author Comment

by:coachjim
ID: 39881358
hgholt:

It works.  Thanks.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39881466
=IFERROR(as it is whole formula, "")

This will overcome with #N/A. Error is replace with ""  I.e  blank.

Thanks
0
 
LVL 2

Author Comment

by:coachjim
ID: 39881591
That works.  I want to accept both solutions, but give you each full points instead of splitting them.  I've learned completely new things from each.

Is there a way I can do that?  I'm guessing accept one here and then do something special?
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39881648
Mr.coachjim,

Thank You for generosity. But as per my opinion Mr.hgholt solution is more suitable then me, as in that you don't have to change anything in your Workbook. & as per standards of excel that is best way to achieve your desire result. it is my bad how do I forgot this .....  :)


Thanks
0
 
LVL 2

Author Comment

by:coachjim
ID: 39882335
Mr. itjockey & Mr. Hgholt

Arg!  That's the sound of frustration.

I appreciate that one solution required a change to the workbook and that a solution that doesn't require a change is more elegant and sometimes critical.

But I loved both solutions and learned equally from them.

And I won't argue with you, Mr. Itjockey.  You say give this one to Mr. Hgholt and I will.

I will also see if I can think of a problem to send your way.

Thanks to you both.

Jim
0
 
LVL 2

Author Closing Comment

by:coachjim
ID: 39882339
This was a first for me.  I've posed questions in the past where several people submitted parts of the ultimate solution and I could easily award shared solutions.

This time I got two great and totally distinct solutions.  Either of which would have been great yet I end up just awarding one as the solution.

I'm delighted and bummed at the same time.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39882344
You are welcomed!!!!!
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39882373
only one thing to mention ( I dint investigated yet) but if there is numerical 2 in source sheet even then COUNTIF returns with 1 or 0. And VLOOKUP returns with actual number.
I don't know how your data is in source sheet but it is just thought.

Thanks
0
 
LVL 2

Author Comment

by:coachjim
ID: 39882428
Mr. ITJockey

Good point and relevant to my application.

I'll get back to you directly on this.

Jim
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question