Match Values on two worksheets by matching names

Hello!

I am having some trouble figuring out how to automate the data for the attached file. Basically what I am looking to do:

1) On the end-result sheet, I have a set of unique names going down a column and their work habits across the top row

2) On the datasheet, the data is similarly laid out

3) what I am hoping to achieve is:

a) have the formula look up the name on the end-result sheet,
b) match it to the name on the datasheet
c) then search for their work habits column and if the answer is "yes" place a "1" in the result sheet, otherwise 0

I have tried vlookup, index, match, index, match, match as array and can't seem to figure out where the issue maybe. Please see the attached file, might be easier to understand what I am trying to do.

Thank you!

Re: Match Values on two worksheets by matching names

thank you very much for your help! I actually ended up using a combination of index match with vlookup due to time constraints. The formula is as follows ( I copied and pasted it from my work, so it is not adjusted for example file above, but I thought it would give you an idea of what I used)

={IF(AND(INDEX($B$7:$B$90,MATCH($B7,Range1,0)),VLOOKUP($B7&"",Range,2,FALSE)="Yes"),0.5,0)} entered as an array formula.

But I believe your formula is more elegant. I will test it today to see how well it works on a large dataset.

Once again, I am very grateful for your help! Looking at your formula helped me tremendously and I was able to correct the logic error in my earlier attempts when I used index match match.

SE