Vlookup that returns look up if a cell on the same row has a specific value

Table 9

A B C
1 john 1/1/12 P
2 Steve 12/1/12 A
3 Khan 1/4/13 A


with the above mock table I'm looking for a vlookup formula or vba that comprises a lookup value that matches the name and looks up the date only if the value in column C is 'P'.



Vlookup example.xlsx

Ive attached a sheet for your viewing.

Many thanks

Hi hb316,
I am new to this but try the example I have attached. Prob is I moved the 3rd column to be the 1st due to the VLOOKUP syntax. Also cells in the answer need to be formatted to date.

Please read down further for corrections noted by other users.

Dexamphetamine your effort was commendable but you posted a simple VLOOKUP formula without any 'Absolute' references hence when you dragged the formula down the cell references are incorrect - see your cells C11, C12 & C13. Your formula looks like this =VLOOKUP(A6,A1:C3,3,0), whereas with a formula you want to drag that has references to fixed cells it should have looked like this: =VLOOKUP($A$6,$A$1:$C$3,3,0)

What you needed to do was make the references in the formula Absolute by selecting the F4 key when you inserted the cell reference in the formula. The $ sign makes a column or row 'Absolute' e.g. $A6 means the column is Absolute and the row in 'Relative', conversely A$6 means the row is Absolute so then $A$6 means both the column AND the row are Absolute so then $A$6 is an 'Absolute cell reference, and in your formula $A$1:$C$3 means the range is also Absolute and will not change when dragged down or across and this is important. An 'Absolute' cell reference will never change.

Cheers

Thanks Ursul,
You are absolutely correct. I forgot about the F4 key (I usually just type it directly into the formula at the time) and as such was only useful for the one cell, with all copied cells being incorrect. Hope hb316 sees your correction before finishing his/her project!

Regards,
Finchy/Dexamphet!

=MAX(INDEX(INDEX($A$2:$A$7=A11,0)*INDEX($C$2:$C$7="p",0)*$B$2:$B$7,0)) should do it