MATCH / INDEX partial text in a single column

Hello,

I have a list of data in column A, from row 3 down hundreds of rows. I want to have a formula in A1 which looks for the three left characters to match the text "ABC". If the value is found then I would like to display the value of the whole cell ("ABCDEF" for example). I have been trying to get MATCH to work but seem to be falling over with incorporating the use of LEFT as well.

Thanks in advance,

JM

=MATCH(LEFT("ABC",A3:A32),A3:A32,1)

Formula:

 =INDEX($A$3:$A$32,MATCH("ABC*",$A$3:$A$32,0)) 

My concern is that you are returning a single result in A1 but this will work

Thanks for that - it works great when the value is present but is returning #N/A when not. Would you recommend putting an IF around it to deal with that or is there a more elegant solution ?!

Thank you for the quick reply. - I am now getting a #NAME? error. - That of course is highlighting an error in formula which I don't see.

Sorry for this.....

No need to apologise, could you upload a sample workbook with the error as I don't get an issue on my side using =IFNA(INDEX($A$3:$A$32,MATCH("abc*",$A$3:$A$32,0)),"value if n/a")

I attach a basic sheet using your code. Ideally I want to just return "" / nothing if the value is not found.

Thanks a lot.

try:

=IFERROR(INDEX($A$3:$A$32,MATCH("abc*",$A$3:$A$32,0)),"")

I assume you're using an older version of excel which doesn't support IFNA

Perfect - thanks !

I am a dinosaur on Excel 2011.... A few of my old macros don't play nicely with the new versions of Office.

Much appreciated

You're Welcome Miller, feel free to add to rep if you think I deserve it