Checking a list of values for a match, using the match to decide where to take data from

Hi, I have attached a dummy document to this so that you can see what I'm looking for.

In the dummy document I want (for each of the cells in Col. D) cell D2, Sheet 2 to display the value of the voucher. It needs to check cell C2 to see if any of the text matches with ANY cell in Col. A, Sheet 1. If it finds a match, then it needs to take the respective price from Col. B, Sheet 1.

Hope that makes sense, thanks in advance.

Dave

How about this formula in cell D2, dragged down:

=IFERROR(INDEX(Sheet1!$B$2:$B$9,MATCH(TRUE,INDEX(ISNUMBER(FIND(Sheet1!$A$2:$A$9,C2)),0),0)),"Voucher not matched")

Thank you so much!

Works like a charm.

Good, I was a bit worried because I wasn't sure if you were on Excel 2007 and the IFERROR function isn't in 2003.

Glad you're sorted.