VLOOKUP when more than 1 data matches

[goble]

Hello,

I have "Sheet1"

Company	Schedule	Date
Microsoft	2 hours (10h00-12h00)	13-Oct-22
IBM	4 hours (09h00-13h00)	15-Oct-22
Dell	3 hours (13h00-16h00)	13-Oct-22

Then "Sheet2",

=TODAY()	=A1+1	=B1+1	=C1+1
=VLOOKUP(A1,Sheet1!A1:C4,????,FALSE)	=VLOOKUP(B1,Sheet1!A1:C4,????,FALSE)	=VLOOKUP(C1,Sheet1!A1:C4,????,FALSE)	=VLOOKUP(D1,Sheet1!A1:C4,????,FALSE)

The question marks are what I don't know what to include. Or may be this is the wrong approach

Assuming we are the 13-Oct-22 today, The results of "Sheet 2" am looking for is hereunder. The idea is to get a planning on sheet2 (from sheet1) as from Today the 13-Oct-22. However VLOOKUP may find more than 1 matched data, then it should have other results on other rows.

"Sheet2":

13-Oct-22	14-Oct-22	15-Oct-22	16-Oct-22
Microsoft - 2 hours (10h00-12h00)		IBM - 4 hours (09h00-13h00)
Dell - 3 hours (13h00-16h00)

Thanks for any hint !

 

[Peter_SSs]

Try this.

goble.xlsm
	A	B	C
1	Company	Schedule	Date
2	Microsoft	2 hours (10h00-12h00)	13-Oct-22
3	IBM	4 hours (09h00-13h00)	15-Oct-22
4	Dell	3 hours (13h00-16h00)	13-Oct-22
5
Sheet1

goble.xlsm
	A	B	C
1	13/10/2022	14/10/2022	15/10/2022
2	Microsoft		IBM
3	Dell
4
Sheet2

Cell Formulas
Range	Formula
A1	A1	=TODAY()
B1:C1	B1	=A1+1
A2:C4	A2	=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$100)/(Sheet1!$C$2:$C$100=A$1),ROWS(A$2:A2))),"")

 

[Peter_SSs]

Sorry, I forgot to include the Schedule

goble.xlsm
	A	B	C
1	13/10/2022	14/10/2022	15/10/2022
2	Microsoft - 2 hours (10h00-12h00)		IBM - 4 hours (09h00-13h00)
3	Dell - 3 hours (13h00-16h00)
4
Sheet2

Cell Formulas
Range	Formula
A1	A1	=TODAY()
B1:C1	B1	=A1+1
A2:C4	A2	=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$100)/(Sheet1!$C$2:$C$100=A$1),ROWS(A$2:A2)))&" - "&INDEX(Sheet1!$B:$B,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$100)/(Sheet1!$C$2:$C$100=A$1),ROWS(A$2:A2))),"")

 

[goble]

This is perfection, thank you so much !

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[goble]

A last thing, If I have a another sheet called sheet11 for example that have the same data format as described in Sheet1 (but with different data).

I want sheet 2 to display as well for this sheet11 For instance :
Sheet2 row2-6 : Display data from sheet1 from Formula as you provided >> this is OK
Sheet2 row7-11: Display data from sheet11 (starting at 2nd row of sheet11)

 

[Peter_SSs]

Same Sheet1 as before.

Sheet11

goble.xlsm
	A	B	C
1	Company	Schedule	Date
2	Co 1	Sch 1	13-Oct-22
3	Co 2	Sch 2	14-Oct-22
4	Co 3	Sch 3	13-Oct-22
5	Co 4	Sch 4	15-Oct-22
Sheet11

New Sheet2 formulas

goble.xlsm
	A	B	C
1	13/10/2022	14/10/2022	15/10/2022
2	Microsoft - 2 hours (10h00-12h00)	Co 2 - Sch 2	IBM - 4 hours (09h00-13h00)
3	Dell - 3 hours (13h00-16h00)		Co 4 - Sch 4
4	Co 1 - Sch 1
5	Co 3 - Sch 3
6
Sheet2

Cell Formulas
Range	Formula
A1	A1	=TODAY()
B1:C1	B1	=A1+1
A2:C6	A2	=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$100)/(Sheet1!$C$2:$C$100=A$1),ROWS(A$2:A2)))&" - "&INDEX(Sheet1!$B:$B,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$100)/(Sheet1!$C$2:$C$100=A$1),ROWS(A$2:A2))),IFERROR(INDEX(Sheet11!$A:$A,AGGREGATE(15,6,ROW(Sheet11!$A$2:$A$100)/(Sheet11!$C$2:$C$100=A$1),ROWS(A$2:A2)-COUNTIF(Sheet1!$C$2:$C$100,A$1)))&" - "&INDEX(Sheet11!$B:$B,AGGREGATE(15,6,ROW(Sheet11!$B$2:$B$100)/(Sheet11!$C$2:$C$100=A$1),ROWS(A$2:A2)-COUNTIF(Sheet1!$C$2:$C$100,A$1))),""))

 

[goble]

Hello,

Perfection again !

Always amazed by the quality of replies from Excel gurus here ! Straight to the point !

 

[Peter_SSs]

Good news! Glad it worked for you.

 

[goble]

Hello,

My excel sheets have got some new contents and I wanted to add some outputs.

Sheet 1 is called : 'Invoice FPB'
Starting at Row1 Column A

Company

Date Invoiced

Services

Date 1

Date 2

Date 3

Date 4

Date 5

Date 6

Date 7

Invoiced

Payment Received

Payment Due

Invoice No.

Place

Client 1

Don't Care

20h-22h

29-Jun-23​

Don't Care

Don't Care

Don't Care

Don't Care

Location 1

Client 2

Don't Care

19h-23h

30-Jun-23​

Don't Care

Don't Care

Don't Care

Don't Care

Location 2

Sheet 1 is called : 'Invoice KPB'
Starting at Row1 Column A

Company	Date Invoiced	Services	Dates		Invoiced	Payment Received	Payment Due	Invoice No.	Type	Place
Client A	Don't Care	18h-22h	29-Jun-23​		Don't Care	Don't Care	Don't Care​	Don't Care	Don't Care	Location A​
Client B	Don't Care	17h-00h	30-Jun-23		Don't Care	Don't Care	Don't Care​	Don't Care	Don't Care	Location B​

Here's the formula I have adapted (as per your solution) and which is working.

=IFERROR(@INDEX('Invoice FPB'!$A:$A,AGGREGATE(15,6,ROW('Invoice FPB'!$A$2:$A$10014)/('Invoice FPB'!$D$2:$D$10014=A$1),ROWS(A$2:A2)))&" - "&@INDEX('Invoice FPB'!$C:$C,AGGREGATE(15,6,ROW('Invoice FPB'!$C$2:$C$10014)/('Invoice FPB'!$D$2:$D$10015=A$1),ROWS(A$2:A2))),IFERROR(@INDEX('Invoice KPB'!$A:$A,AGGREGATE(15,6,ROW('Invoice KPB'!$A$2:$A$10000)/('Invoice KPB'!$D$2:$D$10000=A$1),ROWS(A$2:A2)-COUNTIF('Invoice FPB'!$D$2:$D$10014,A$1)))&" - "&@INDEX('Invoice KPB'!$C:$C,AGGREGATE(15,6,ROW('Invoice KPB'!$B$2:$B$10000)/('Invoice KPB'!$D$2:$D$10000=A$1),ROWS(A$2:A2)-COUNTIF('Invoice FPB'!$D$2:$D$10014,A$1))),""))

Actual Output :

29-June-2023	30-June-2023
Client 1 - 20h-22h	Client 2 - 19h-23h
Client A - 18h-22h	Client B - 17h-00h

New output that I need:

29-June-2023	30-June-2023
Client 1 - 20h-22h - Location 1	Client 2 - 19h-23h - Location 2
Client A - 18h-22h - Location A	Client B - 17h-00h - Location B