You are a genius! Thank you so much!!!

"Max" wrote:

Here's a working sample for easy reference:
http://www.flypicture.com/download/MTU4NDA=
Fiona.xls

(note: if reading this from microsoft's webpage, do a copy n paste of the
entire link into your browser, including the "=" at the end. do not click on
the link direct.)

Array-entered in E4, press CTRL+SHIFT+ENTER to confirm the formula:
=IF(D4="","",INDEX(AC$4:AC$55,MATCH(1,(ISNUMBER(SE ARCH(AB$4:AB$55,D4)))*(AB$4:AB$55<""),0)))
Copy E4 down

I switched it around within the SEARCH. Based on a 2nd reading of the orig
post you probably have a mixture of text in your source col D. The above
should be plug-n-play, it's made based on your adaptation.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fiona" wrote:
Thanks Max,
I've tried this formula, but it comes up with #N/A. I've made it an array
formula but its not working. I think I may have done something wrong!
I've written it to match the coloumns I have on my spreadsheet as follows:-
{IF(D4="","",INDEX(AC$4:AC$55,MATCH(1,(ISNUMBER(SE ARCH(D4,AB4:AB55)))*(AB4:AB55<""),0)))}

Thanks again for your help!
Fiona