Formula that takes info, then finds name, searches for name in another, then enters date

[Wiggyknows]

Hi everyone,
So I looked all over for something similar and couldn't find anything quite like what I needed. I've tried many different Formulas and just can't figure it out.

So I'm looking for a formula that looks in sheet 2, if column B says Yes, it looks at the name in column A, it then finds the name in sheet 1 column A and enters date in column B in sheet 1 as well.


For example:

Sheet 1
column A column B
Sheridan Morris
William Smith

Sheet 2
column A column B
Sheridan Morris 1
William Smith 0


Sheet 2 has the name in column A and a number 1 or 0 in column B(I have a bunch of other formulas in play that is why it will be a number 1 or 0 just for easier calculations). I want the formula to say, if there is a number 1 in column B(Sheet 2), it then looks at the name in column A(Sheet 2) and finds that exact name in Sheet 1 column A, then enters today's date in column B(Sheet 1).

The wanted end result:

Sheet 1
column A column B
Sheridan Morris 05/08/2014
William Smith N/A

Sheet 2
column A column B
Sheridan Morris 1
William Smith 0


I hope this makes sense so if not please just say something and any help is much appreciated, thank you!

[Glenn Kennedy]

Hi there. I think that this is what you asked for...

[Glenn Kennedy]

Let's attach the attachment, this time!!

[Wiggyknows]

Thanks Glenn! so I keep inputting that into my spredsheat, changing the column ranges and I it just keeps returning 0 for some reason... Here is what I have:

=IFERROR(IF(INDEX(Sheet2!$R$6:$R$20,MATCH(Sheet1!B276,Sheet2!$O$6:$O$20,0))=1,NOW(),"0"),"0")

The names in Sheet 2 are on lines R6-R20. The name in Sheet 1 is on the line B276 and the number 1 or 0 is on lines O6-O20.
Is there something I'm missing?

[Glenn Kennedy]

I'm losing the plot a bit here. the answer is obvious, but it's taken me 20 mins to spot it. The INDEXfunction is meant to return either the 1 or the 0. Your transposition of my formaul has a slight error.

So, using your cell references, use:

Please Login or Register  to view this content.

OK now??

[Wiggyknows]

Glenn, you sir are a life saver!!! This will save us hours of time per week, thank you so much!!