Multiple lookups in single cell

[koprich]

Hi All!

I am looking for some help with creating a dynamic meeting log/calendar for my team. There's a couple of elements to my questions below but if you could help with any one part of it I'd be immensely grateful!

I have a log with columns such as "Meeting Date", "Client", "Attendee" and have also set up month calendars on separate tabs which automatically update the dates according to a “Year” toggle using Janoffset, Feboffset, etc.

What I would like to do is have these calendars automatically populate a cell beneath each date with any meetings on that day. The client name and then a space then the attendee in brackets would be sufficient and with each meeting having its own line in the cell)

I have gotten a Vlookup working but have run into two snags: 1) where there is more than one meeting on a single day 2) I can only return one piece of information in the cell or otherwise have it blank, I can’t have the client name AND attendee.

Is there any way to lookup all of that information in the one cell or is this just a pipe dream?

I have attached my workbook which contains my workings so far and in the first cell of Jan shown the ‘ideal’ format I’d like the data in.

Thanks in advance!

[ARGK]

Hi
Try this formula in C7 of your sample:

=IF(ISNA(VLOOKUP(C6,Log!$A$2:$C$200,2, FALSE)), "", VLOOKUP(C6,Log!$A$2:$C$200,2, FALSE)) & " (" & VLOOKUP(C6,Log!$A$2:$C$200,3, FALSE) & ")"

Good luck.
Tony

[koprich]

Fantastic! Thank you! That solves the how to add additional information into the cells.

I still need a solution for where there are multiple entries on the same date. Any suggestions?

[Glenn Kennedy]

Blast. i had just written out an explanation and something went pop and i lost it.

I have done a few things here. Basically, your request is very difficult (at least for me) so I have cheated - but the end result looks OK. Instead of a single cell per day, there are now 4 - but it looks like one.

I have created a couple of helper rows in LOG to help with the equations later. I have modified the date offset equation slightly, to get the dates in exactly the same format as the helper row. This may not be necessary, but it's been done.

I have put the array formula (these are set by CTRL + SHIFT + ENTER) only in for the first week of the month, so that you can see what you think. It will need to be customised for the other weeks, but I'm not going to do that until I'm sure that you are happy with what I have got so far. You can let me know.

Have a fiddle around with it & let me know what you think...

[Glenn Kennedy]

I knew there was something else... If I were you, i would try to do away with the 12-sheet solution and go for a one-sheet solution, making better use of the dropdown box in row B. Views?

[Glenn Kennedy]

It's a terrible addiction, this Excel. I was thinking about htis and decided to complete January's sheet. I ran into another problem. The way you had set up the numerical dates of the calendars there were two 29th's, two 30th's etc. This meant that the same engagements came up twice. So i modified the offset equations a bit more so only January's days appear against January. how does this look now?

[newdoverman]

Here is a possible solution for you. I just applied it to Dates Jan 5 to Jan 11. I changed the dates in the date rows to match the dates in the log and for the data for each date there are 4 rows that appear to be merged but are only formatted to appear that way.

The following formula is entered in B7 and copied down and across (Array Formula - Ctrl + Shift + Enter)

Please Login or Register  to view this content.

[Glenn Kennedy]

I think that newdoverman's solution may have the same problem (which I fixed in mine) on those dates 28, 29, 30, 1,2,3, etc that can appear twice on the sheet

[newdoverman]

Here is the calendar more filled out. The formula depends upon the actual date in the header for each day so is not influenced by the actual number of the day.

This is only for January but shows the lead in and lead out dates for the 1st and last week.

[Glenn Kennedy]

Neat! Good job there.

[newdoverman]

I find these calendars a real pain to work with to get what you want.

Here is one that may be able to be adapted to solve the problem. I found it in a collection of old files. The author is given but I don't know where I got it from. It uses lookups but they can probably be replaced...don't have the time right now to really have a good look at it.

[koprich]

Thank you both for the helpful responses. I tried to combine the single-tab calendar above with the formulas you designed specifically for my log and it doesn’t seem to be working for me.

I even tried parts of the formula with an ultra-simplistic model (attached) and it still wasn’t working for me.

Would you be able to explain where I am going wrong with it and what I should be doing?

Many thanks


simple lookup example.xlsx

[newdoverman]

You didn't ARRAY ENTER (Ctrl + Shift + Enter) the formula.

[newdoverman]

You didn't ARRAY ENTER (Ctrl + Shift + Enter) the formula.

Here is the upload that I last sent converted (I think) to use your data. All entries would be made on the Log worksheet in order for the worksheet to work. I have only made limited tests so work with it before using it for real.

[koprich]

This works brilliantly, thank you. I've been working through the formula for it and it all makes sense besides the section that reads:

IF(Log!$A$2:$A$200=B$4,ROW(Log!$A$2:$A$200)-MIN(ROW(Log!$A$2:$A$200))+1)

I don’t understand the reasoning for this, especially the "Row of date in log - min of Row of date in log + 1" section.

Is there a way to add a filter whereby you can filter for only specific attendees? Say, if I only wanted to see what meetings "JS" has this month, I could select JS from a dropdown and only her meetings appear. This would be a particularly useful feature for my team so they can quickly see when a specific person has a client meeting.

[newdoverman]

IF(Log!$A$2:$A$200=B$4,ROW(Log!$A$2:$A$200)-MIN(ROW(Log!$A$2:$A$200))+1)

IF(Log!$A$2:$A$200=b$4 is the condition that the IF is looking for and that is, is there a match for the date in B4 in the column A of the Log worksheet. The actual return of this part of the formula is: {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE.... for 200 rows. You can see that the first two returns are TRUE. This means that the first two rows in the range Log!$a$2:$a$200 match the date in B4.

ROW(Log!$A$2:$A$200)
This gives a listing of all the row numbers in the range $A$2:$A$200 .... 2;3;4; etc. up to 200

MIN(ROW(Log!$A$2:$A$200))

This gives the MINIMUM row number in the range $A$2:$A$200 which is 2. If this is subtracted from ROW(Log!$A$2:$A$200) the result is 0. You don't want to return the value from row 0 you want the first row of the range so +1 is added.

All of this together is the ARRAY part of the SMALL function just before the IF. The ROWS($1:1) is a counter for the K part of the SMALL function and increases as the formula is copied down the column giving the smallest value then the second smallest etc.


The FILTERING that you want to do has to be done on the Log worksheet. This will not show up on the calendar but will only show on the Log worksheet. Click anywhere in the data on the Log worksheet then click on the Data Tab, Filter and filter buttons will appear beside all the headings on the Log worksheet. This will allow you to filter the records in place without disturbing your entire data set.

[koprich]

Thanks again for the swift and helpful response newdoverman. I wish it would let me rep you again for all the assistance.

I'm still not quite following you on this point but this is due to my lack of excel experience not your explanation so forgive me.

As I understand it, for where date in log=date in calendar we just want the list of rows where this is true for the SMALL function (which will chose the kth value).

I guess the point I was making is since ROW(Log!$A$2:$A$200)-MIN(ROW(Log!$A$2:$A$200))+1 is always equal to 1 this doesn’t seem to yield an array where date in log=date in calendar.

As for the filtering, it'd be nice to be able to filter the calendar itself by all attendees or an individual attendee but I'm guessing that's a huge undertaking involving macros or vba coding or other things which I don’t understand. If it can’t be done relatively simply or formulaically then it's probably best left out. EDIT: I've figured out how to do this by using an IF formula and adding the attendee name to the date in a unique column in the data and looking up that

[newdoverman]

The calendar itself is only a "presentation" of your data in a format that is difficult to deal with to extract records from. The basic data is where the various "presentations" should be generated.

Back to the formula.
ROW(Log!$A$2:$A$200)-MIN(ROW(Log!$A$2:$A$200))+1 will equal 199 which defines the range rows to be considered by the SMALL function. The SMALL(IF(Log!$A$2:$A$200=G$4,ROW(Log!$A$2:$A$200)-MIN(ROW(Log!$A$2:$A$200))+1),ROWS($1:1)) is supplying the INDEX function with a row to retrieve a value from. The INDEX(Log!$B$2:$B$200 indicates what column the value wanted is in. Combine this with the row number, you have an intersection of column and row from which the value is retrieved.

You can see this if you click on a cell with this formula and select this part of the formula in the formula bar then hit F9. The value that the selection represents will be given to you.

Be sure to hit Ctrl + Z to return to the regular formula or the result will remain what the F9 gave you....you don't want that!!