Extract data from another table based upon multiple criteria

Darren Guess

New Member
Joined
Dec 7, 2011
Messages
28
Good Morning

I have been watching a number of 'Excel is fun' videos by Mike Girvin to try and understand formulas better and solve my problem, but I still can't crack it. I'm hoping you guys can help me out.

I have a data table that has a list of tasks in column 1 (as below) and a calendar section that displays True or False for when the tasks are planned. I have another data set that has a list of tools required. As you can see from the 'Standard Vlookup' column there are a number of tasks that do not have any tooling allocated.

Now to the problem: What I would like to be able to do is to extract the tool number from table 2 if the calendar has a 'True' and the Task Ref has a tool allocated to it. Secondly when the results are returned I would like the data collapsed so that there are no gaps (blank values) in the table. The result would look as per the data at the bottom of table 1.

I've tried some Index and Match formulas, and some more involved Vlookups, but I'm obviously missing something.

Thanks in advance
Darren

Tue</SPAN></SPAN>
Wed</SPAN></SPAN>
Thu</SPAN></SPAN>
Fri</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
3</SPAN></SPAN>
4</SPAN></SPAN>
Task Ref</SPAN></SPAN>
09-Sep Day</SPAN></SPAN>
09-Sep Night
10-Sep Day</SPAN></SPAN>
10-Sep Night
11-Sep Day</SPAN></SPAN>
11-Sep Night</SPAN></SPAN>
12-Sep</SPAN></SPAN>
Standard Vlookup</SPAN></SPAN>
34T0001</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
29X1301CMR</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
21C3601</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
A21010-70</SPAN></SPAN>
21C3602</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
52C1003</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
52C1004</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
21C3301</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C2301</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C2302</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C3301</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
A27021-69</SPAN></SPAN>
27C3302</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4301</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4302</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4303</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>

TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
A27021-29</SPAN></SPAN>
27C4304</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4305</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4306</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4307</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C4308</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C5301</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
27C5302</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
TRUE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
FALSE</SPAN></SPAN>
A21010-70</SPAN></SPAN>

A21010-70</SPAN></SPAN>
A27021-69</SPAN></SPAN>

A27021-69</SPAN></SPAN>

A27021-69</SPAN></SPAN>

A27021-69</SPAN></SPAN>

A27021-29</SPAN></SPAN>

A27021-29</SPAN></SPAN>
A27021-29</SPAN></SPAN>

A27021-29</SPAN></SPAN>

<TBODY>
</TBODY>


Task Ref</SPAN></SPAN>
Tool No</SPAN></SPAN>
12L2126-1</SPAN></SPAN>
B27008-1</SPAN></SPAN>
12L2126-2</SPAN></SPAN>
B27008-1</SPAN></SPAN>
21C3601</SPAN></SPAN>
A21010-70</SPAN></SPAN>
21C5601-1</SPAN></SPAN>
A21001-44</SPAN></SPAN>
21C5601-2</SPAN></SPAN>
A21001-44</SPAN></SPAN>
23C7501</SPAN></SPAN>
42A12</SPAN></SPAN>
23C7501</SPAN></SPAN>
ATS-260</SPAN></SPAN>
23C7501</SPAN></SPAN>
DUKANE PL-1</SPAN></SPAN>
23C7501</SPAN></SPAN>
PL-3</SPAN></SPAN>
24C4501</SPAN></SPAN>
F70284-1</SPAN></SPAN>
26C2304</SPAN></SPAN>
A26001-187</SPAN></SPAN>
26C2306</SPAN></SPAN>
A26001-187</SPAN></SPAN>
26C2601</SPAN></SPAN>
B26002-1</SPAN></SPAN>
27C0601</SPAN></SPAN>
B20003-23</SPAN></SPAN>
27C0602</SPAN></SPAN>
B20003-23</SPAN></SPAN>
27C3301</SPAN></SPAN>
A27021-69</SPAN></SPAN>
27C3301</SPAN></SPAN>
A27021-98</SPAN></SPAN>
27C3301</SPAN></SPAN>
B27023-48</SPAN></SPAN>
27C3301</SPAN></SPAN>
B27039-13</SPAN></SPAN>
27C3301</SPAN></SPAN>
DPPH-100</SPAN></SPAN>
27C4303</SPAN></SPAN>
A27021-29</SPAN></SPAN>
27C4303</SPAN></SPAN>
A27021-98</SPAN></SPAN>

<TBODY>
</TBODY>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I would use a VLOOKUP with some further testing. E.g., in I4:

Code:
=IF(COUNTIF(B4:H4,"TRUE")>0,IFERROR(VLOOKUP(A4,LookupPage!A:B,2,FALSE),""),"")
As for collapsing, could you just use autofilter and filter out the blanks?

Hope this helps,

Chris.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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