VLOOKUP return multiple results

Welcome to the forum.

I don't see where in your sample workbook (which, by the way, is far too big and busy to be a true sample) you have mocked up what you want to do. I see only empty tables.

Show us (manually) what you want - add some annotation to your workbook and/or highlight relevant cells/ranges to make what is blindingly obvious to you clear to us. Thanks.

This article describes a method of extracting data from multiple worksheets based on criteria

https://excelxor.com/2014/10/16/coll...on-conditions/

VLOOKUP will only return the first matching value it finds.

Not sure Power Query will extract data in its current format. VBA is an alternative.

This is a relatively simple job for O365, but is a BIG bit problematic in earlier versions! I thought I had it sorted.... but I'm still stuck.

I've just had a breakthrough!! It has taken a couple of hours, off-and-on. I just now need to pull it all together. Should be done by beer o'clock Irish evening time (45 mins to you)

I've hit a snag or two.

1. Below each date you have ONE cell. How are your proposing to get (potentially) several names and several infractions into one cell?

2. Can I rearrange January appropriately to show a maximum of ?? 4 ?? infractions on any single day?

3. Setting this up for 12 months will be a tad tedious I'll do it for one month... but NOT for all 12... Would you be prepared to consider an alternative structure? You display ONE month at a time, with the month selected via a dropdown box?

VBA

In sheet "CALENDAR": right-click on Tab name and "View Code"

Please Login or Register  to view this content.

In the main "module"

Please Login or Register  to view this content.

Double_click on a date in "CALENDAR" to activate: currently data for 1st and 2nd January

Output in AO8 table in CALENDAR

I have made it much easier to "do" a year. I will "do" January and then stop... I'm going cross-eyed.

I do not propose to explain ANYTHING yet. You need to test it for JANUARY only. DO NOT try to copy it across the year ... yet.

There is a "bug". You will need to ensure that BOTH a date AND a REASON are entered. I'm sure we can fix that with data validation a bit later.

As you look at it... I have created 4 Named Ranges (sheets, date, name & offence). Sheets is different from Lookup_sheets.

Will you me adding many more staff positions (i.e. sheets)?? I NEED to know.

I changed C13 downwards in all your individual's sheets.



That's me for now. You'd better like it....

Ummm. Who are you talking to? me with a formula or John, with VBA?

So. Which way do you want to go? Formula or VBA??? There's no point in me going further with this if you intend to use VBA....

@Glenn: FYI

The VBA solution does not update the Calendar directly; it puts the results into a table (AO8) which the OP had set up at one time.

Using the layout you adopted for your formula solution i.e. 4 lines per date would be more amenable to the VBA approach but still a pain!

If you don't mind to use a lot of helper cells.

1. Build builder sheet.

enter all sheet name in column D and include column / row start / row stop of date field in each sheet
(I think all are the same but provide just in case)

count occurance of record
H5
=IF(D5<>0,IFERROR(COUNTIF(INDIRECT("'"&D5&"'!"&E5&F5&":"&E5&G5),">0"),0),0)

setup starter of all per sheet
J5
=IF(D5>0,IF(MAX(J$4:J4)=0,IF(SUM(H$4:H5)>0,1,SUM(H$4:H4)),SUM(H$4:H4)+1),0)

build ALL data area
M5
=IF(L5<=$J$2,IF(LOOKUP(L5,$J$4:$J$26,$D$4:$D$26)=0,M4,LOOKUP(L5,$J$4:$J$26,$D$4:$D$26)),"")

N5 (in case sheet name is not the same to employee name)
=IF($L5<=$J$2,INDIRECT("'"&$M5&"'!D2"),"")

O5 (row of each record)
=IF($L5<=$J$2,IF($M4<>$M5,MATCH($N5,INDIRECT("'"&$M5&"'!C:C"),0),MATCH($N5,INDIRECT("'"&$M5&"'!C"&O4+1&":C"&G5),0)+O4),0)

P5
=IF($L5<=$J$2,INDIRECT("'"&$M5&"'!B"&$O5),"")

Q5
=IF($L5<=$J$2,INDIRECT("'"&$M5&"'!D"&$O5),"")

S5 (merge each date together)
=IF($L5<=$J$2,IF(COUNTIF($P$4:$P5,$P5)=1,$N5&" "&$Q5,IFERROR(LOOKUP(2,1/($P$4:$P4=$P5),$S$4:$S4)&CHAR(10)&$N5&" "&$Q5,$N5&" "&$Q5)),"")
Note : I've add char(10) [ line feed ] for new line in the output

Then , back to CALENDAR sheet
H11
=IFERROR(LOOKUP(2,1/(Builder!$P$4:$P$221=H10),Builder!$S$4:$S$221),"")
Note : output become one cell, so need to adjust row height and width

Regards.

Please Login or Register  to view this content.

Minor modification to go through all sheets and extract ALL the items and place in table in AO9. "RUN" button calls macro.

My own view is that such a list is better than filling in your calendar which requires much scrolling to focus on any month.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.