Lookup Multiple Data Points and Return Value

[helpexcel]

I have data on sheet1 that is organized with a multiple lines for each Car. I'm trying to consolidate the data on Sheet2. I have copied and removed duplicates to sheet2, and i'm wondering if there is an easy way to achieve the bottom chart. Basically, I'd like to insert a new column to B and say - Look at column B and D, if they match and column C says "tires' then place the name in Sheet1 Column A in Sheet2 Column B that matches columns B and D. Hope that makes sense.


Person Car Part Drop Off
Bill Ford Oil Wednesday
Pete Ford Tires Wednesday
Kate Chevy Battery Thursday
Kate Chevy Tires Thursday
Amy Dodge Oil Monday
Amy Dodge Tires Monday
Bill Toyota Tires Monday
Jeff Honda Battery Tuesday
Jeff Honda Tires Tuesday



Person Tire Person Car Drop Off
Bill Pete Ford Wednesday
Kate Kate Chevy Thursday
Amy Amy Dodge Monday
Bill Toyota Monday
Jeff Jeff Honda Tuesday

 

[helpexcel]

So i have an array formula in the cell that kind of does what I need it to. Is there a way to use the OR function? Something like this - Which doesn't work (Or(Data6=Sheet1!$D$4,Data6=Sheet1!$D$5))

How would I use VBA to do this?


=INDEX(Data1,MATCH(1,(Data2=$E3)*(Data3=$O3)*(Data4=$J3)*(Data5=$I3)*(Data6=Sheet1!$D$5),0))

 

[FlameRetired]

Quick question.

The headers in the output section do not appear to agree with the output data or instructions.

Ex: There is no input Bill Ford Tires Wednesday --- although there is output Bill Pete Ford Wednesday The output pattern does not seem consistent with the rest.

What am I missing?

 

[helpexcel]

The data is switched around so that there is a new column for "tire person". Basically each car is being put on 1 row instead of 2. Its a very basic example, and the formula I posted works, but it doesn't work if I want to use multiple lookups.

What i want to accomplish is looking at the data and saying match Data2, 3, 4, 5 to columns E, O, J, and I. Then look at Data6 and see if it's in another set of data. If all of them are true, then put Data1 in column A.