Formula to return a date from a table

[Golf73]

I am looking for some assistance with a formula in Excel.

I have a table which consists of text, numbers and date in each of the columns, which I have set out below.

Column A Column B Column C
30/2/13 1000 Yes

What I am looking to do is to return the date for the corresponding yes appears using VLOOKUP(Yes,A1:C5,1,FALSE)

However i receive the value #N/A

Is there an error in the method I am using or is there a better formula to use to return the information that I am looking for.


Thanks

[BadlySpelledBuoy]

Several things wrong with that formula....

Try =index(a1:a5,match("yes",c1:c5,0))

[BadlySpelledBuoy]

I will point out what's wrong with the formula here.

You're trying to use VLOOKUP alone to perform a "Left Lookup", i.e. return a value that is to the left of the value you're using as the identifier. That doesn't work.
Even if it did, you'd need to put Yes in quotes as you're looking up a text value.

[alansidman]

Vlookup works on a basis of starting on the left most column for finding the matching value.
For your situation to work, you would have to have your Yes values in the left most column or employ the Index-Match functions.

http://www.contextures.com/xlFunctions03.html

[Golf73]

Thanks for your reply. I have reformatted my table so that the value that I am searching on is on the left hand side. I now get a date returned but it is not the date that corresponds with this entry but is the last date within the table.

Any ideas?

I have tried the index and match functions but I am confused on how to use it.

[BadlySpelledBuoy]

Could you post some sample data in a workbook and explain which results you expect?

[Golf73]

I had another go using the formula you gave me for INDEX and MATCH and gives me what I am looking. Thanks for your help this has helped a lot

[BadlySpelledBuoy]

No problem. Happy to help.

If you're satisfied you have a working solution, please mark the thread as solved.

BSB.