Excel lookup using multiple criteria

K

kirandhawan

New Member
Joined
Jan 6, 2016
Messages
6
Hi,
Below is a sample table which I need to extract data from:
[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD]Event Name[/TD]
[TD]Location[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]04-Feb[/TD]
[TD]05-Feb[/TD]
[TD]06-Feb[/TD]
[TD]07-Feb[/TD]
[TD]08-Feb[/TD]
[TD]09-Feb[/TD]
[TD]10-Feb[/TD]
[TD]11-Feb[/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-1[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-2[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-2[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-3[/TD]
[TD]Loc-1[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD]Event Name[/TD]
[TD]Location[/TD]
[TD]1st From Date[/TD]
[TD]1st To Date[/TD]
[TD]2nd From Date[/TD]
[TD]2nd To Date[/TD]
[TD]3rd From Date[/TD]
[TD]3rd To Date[/TD]
[TD]4th From Date[/TD]
[TD]4th To Date[/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-1[/TD]
[TD]04-Feb[/TD]
[TD]05-Feb[/TD]
[TD]08-Feb[/TD]
[TD]09-Feb[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-2[/TD]
[TD]05-Feb[/TD]
[TD]06-Feb[/TD]
[TD]10-Feb[/TD]
[TD]11-Feb[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-3[/TD]
[TD]07-Feb[/TD]
[TD]07-Feb[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[TD]Null[/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

can someone help me please?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
kirandhawan said:
can someone help me please?
Click to expand...
If I understood you well, try the following:

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

[TABLE="width: 542"]
<tbody>[TR]
[TD]Event Name[/TD]
[TD]Location[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]04-Feb[/TD]
[TD]05-Feb[/TD]
[TD]06-Feb[/TD]
[TD]07-Feb[/TD]
[TD]08-Feb[/TD]
[TD]09-Feb[/TD]
[TD]10-Feb[/TD]
[TD]11-Feb[/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-1[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-2[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-2[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-3[/TD]
[TD]Loc-1[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
Rich (BB code): 
=IF(Sheet1!C3="Yes";Sheet1!C$2;"")

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl66, width: 64"]04-Feb
[/TD]
[TD="class: xl66, width: 64"]05-Feb[/TD]
[TD="class: xl66, width: 64"]06-Feb[/TD]
[TD="class: xl66, width: 64"]07-Feb[/TD]
[TD="class: xl66, width: 64"]08-Feb[/TD]
[TD="class: xl66, width: 64"]09-Feb[/TD]
[TD="class: xl66, width: 64"]10-Feb[/TD]
[TD="class: xl66, width: 64"]11-Feb[/TD]
[/TR]
[TR]
[TD="class: xl65"]04-Feb[/TD]
[TD="class: xl65"]05-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]08-Feb[/TD]
[TD="class: xl65"]09-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]05-Feb[/TD]
[TD="class: xl65"]06-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]10-Feb[/TD]
[TD="class: xl65"]11-Feb[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]07-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]07-Feb[/TD]
[TD="class: xl65"]08-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]04-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]09-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]04-Feb[/TD]
[TD="class: xl65"]05-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]09-Feb[/TD]
[TD="class: xl65"]10-Feb[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]06-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]09-Feb[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


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)
Rich (BB code): 
=IFERROR(INDEX(helper!$A2:$G2;SMALL(IF(helper!$A2:$G2<>"";COLUMN(helper!$A2:$G2)-COLUMN(helper!$A2)+1);COLUMN(helper!A1)));"")

[TABLE="width: 542"]
<tbody>[TR]
[TD]Event Name[/TD]
[TD]Location[/TD]
[TD]1st From Date[/TD]
[TD]1st To Date[/TD]
[TD]2nd From Date[/TD]
[TD]2nd To Date[/TD]
[TD]3rd From Date[/TD]
[TD]3rd To Date[/TD]
[TD]4th From Date[/TD]
[TD]4th To Date[/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-1[/TD]
[TD]04-Feb[/TD]
[TD]05-Feb[/TD]
[TD]08-Feb[/TD]
[TD]09-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-2[/TD]
[TD]05-Feb[/TD]
[TD]06-Feb[/TD]
[TD]10-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-1[/TD]
[TD]Loc-3[/TD]
[TD]07-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-1[/TD]
[TD]07-Feb[/TD]
[TD]08-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-2[/TD]
[TD]04-Feb[/TD]
[TD]09-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-2[/TD]
[TD]Loc-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-3[/TD]
[TD]Loc-1[/TD]
[TD]04-Feb[/TD]
[TD]05-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-3[/TD]
[TD]Loc-2[/TD]
[TD]09-Feb[/TD]
[TD]10-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event-3[/TD]
[TD]Loc-3[/TD]
[TD]06-Feb[/TD]
[TD]09-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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 (; )
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
197
RobP
R
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
354
dilshod_k
D
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
784
mst3kr
M
tazeo
  • Question Question
Hierarchical Table to a PivotTable (or something similar)
Replies
0
Views
60
tazeo
tazeo
T
  • Question Question
Retrieve info with different date formats
2
Replies
11
Views
242
tigerzen
T

Forum statistics

Threads
1,223,748
Messages
6,174,270
Members
452,553
Latest member
red83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top