Excel lookup using multiple criteria

[kirandhawan]

Hi,
Below is a sample table which I need to extract data from:

Event Name	Location	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb
		04-Feb	05-Feb	06-Feb	07-Feb	08-Feb	09-Feb	10-Feb	11-Feb
Event-1	Loc-1	Yes	Yes			Yes	Yes
	Loc-2		Yes	Yes				Yes	Yes
	Loc-3				Yes
Event-2	Loc-1				Yes	Yes
	Loc-2	Yes					Yes
	Loc-3
Event-3	Loc-1	Yes	Yes
	Loc-2						Yes	Yes
	Loc-3			Yes			Yes

<tbody>
</tbody>

From Above table, I need to extract a consolidated data in below format as the "yes" on a specific date may be shifted to some other date in master sheet and I need to automatically extracted data in below format in adjacent worksheet in the same master file:

Event Name	Location	1st From Date	1st To Date	2nd From Date	2nd To Date	3rd From Date	3rd To Date	4th From Date	4th To Date
Event-1	Loc-1	04-Feb	05-Feb	08-Feb	09-Feb	Null	Null	Null	Null
Event-1	Loc-2	05-Feb	06-Feb	10-Feb	11-Feb	Null	Null	Null	Null
Event-1	Loc-3	07-Feb	07-Feb	Null	Null	Null	Null	Null	Null
Event-2	Loc-1
Event-2	Loc-2
Event-2	Loc-3

<tbody>
</tbody>

can someone help me please?

 

[navic]

can someone help me please?

If I understood you well, try the following:

Step 1
On Sheet1 original data start in A1
Range C3:J11 contains conditions Yes/empty

Event Name	Location	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb
		04-Feb	05-Feb	06-Feb	07-Feb	08-Feb	09-Feb	10-Feb	11-Feb
Event-1	Loc-1	Yes	Yes			Yes	Yes
	Loc-2		Yes	Yes				Yes	Yes
	Loc-3				Yes
Event-2	Loc-1				Yes	Yes
	Loc-2	Yes					Yes
	Loc-3
Event-3	Loc-1	Yes	Yes
	Loc-2						Yes	Yes
	Loc-3			Yes			Yes

<tbody>
</tbody>

Step2
Create a sheet with the name of "helper"
In range A1:H1 header set the dates.
In cell A2 put this formula. Copy the formula to the right and down

=IF(Sheet1!C3="Yes";Sheet1!C$2;"")

04-Feb	05-Feb	06-Feb	07-Feb	08-Feb	09-Feb	10-Feb	11-Feb
04-Feb	05-Feb			08-Feb	09-Feb
	05-Feb	06-Feb				10-Feb	11-Feb
			07-Feb
			07-Feb	08-Feb
04-Feb					09-Feb
04-Feb	05-Feb
					09-Feb	10-Feb
		06-Feb			09-Feb

<tbody>
</tbody>

Step 3
On Sheet1 eg. in cell C16 put this ARRAY or CSE formula. Copy the formula to the right and down. You need finish formula with Ctrl+Shift+Enter (not just enter)

=IFERROR(INDEX(helper!$A2:$G2;SMALL(IF(helper!$A2:$G2<>"";COLUMN(helper!$A2:$G2)-COLUMN(helper!$A2)+1);COLUMN(helper!A1)));"")

Event Name	Location	1st From Date	1st To Date	2nd From Date	2nd To Date	3rd From Date	3rd To Date	4th From Date	4th To Date
Event-1	Loc-1	04-Feb	05-Feb	08-Feb	09-Feb
Event-1	Loc-2	05-Feb	06-Feb	10-Feb
Event-1	Loc-3	07-Feb
Event-2	Loc-1	07-Feb	08-Feb
Event-2	Loc-2	04-Feb	09-Feb
Event-2	Loc-3
Event-3	Loc-1	04-Feb	05-Feb
Event-3	Loc-2	09-Feb	10-Feb
Event-3	Loc-3	06-Feb	09-Feb

<tbody>
</tbody>

I hope that is help you

btw: Pay attention, you may need as a separator in the formula use a comma (,) instead of a semicolon (; )

 

[kirandhawan]

It worked like Magic!! You are a genius!!!
Thanks a Ton!!!