Need a formula that incorporate vlookup or similar

[richjohnson311]

I need help with a formula that will list all of the unit #'s in a column based upon the results of 2 different columns. The range for this is approx. 200 rows. Example:


A B C
Unit # Inspection Date
100 Pass 4/12/14
101 Pass 4/20/14
102 Fail 4/23/14
103 Pass 4/21/14


The formula would look at columns B & C and if it said "Pass" and the Date was within the last week, it would list the Unit # (for all 200 applicable rows). So the result would be in this format: 101, 103, etc.


Thanks so much for your help!

 

[BlueHornet]

Welcome to the board!

Where to begin with this ... ?

In the first place, with the data as you have collated it, VLOOKUP is completely out of the question, because it can't look to the left of the value you want to look up, which in this case is the date field.

In addition, since you want / expect multiple returns, it's going to be awkward for a formula in any case (maybe some kind of array formula would work, I suppose, if you can write it, but I'm not up to that right now).

So my suggestion would be:
1. Add a helper column to check whether the inspection date is within your check range with a True / False condition. That's simple enough.
2. At that point you can simply filter on that column as well as the "Results" column to obtain a filtered list of Units that have passed inspection within your specified time period.
3. Or you could build a simple sort of pivot table to accomplish the same thing.

The filter is probably a better way to go, though. Simpler, for one thing. That pivot table was just ... weird.
Sheet5

	A	B	C	D
1	Unit #	Result	Inspection Date	Within Last Week?
3	101	Pass	20-Apr-14	Yes
5	103	Pass	21-Apr-14	Yes

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula D3 =IF( DATEDIF( C3, NOW(), "d") <= 7, "Yes", "No") D5 =IF( DATEDIF( C5, NOW(), "d") <= 7, "Yes", "No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Filter conditions:
1. Result = "Pass"
2. Within Last Week = "Yes"

Once you have the filtered list you can do whatever other processing you need to with it.