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

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

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