Extract data from another table based upon multiple criteria

[Darren Guess]

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>

 

[Chris Mack]

I would use a VLOOKUP with some further testing. E.g., in I4:

=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.