In match search how to display "NO DATA" if no instance of criteria

Hi I have adapted a formula to search a range for the last instance of an entry with the letter "A" prefix, it then displays it on a sheet relative to its item number. Took me a while because I hadn't noticed the item number I used first had no instance of an "A" and therefore was blank. How can I change formula to display "NO_DATA" when this occurs.

Any help appreciated

Rgds Nigel

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",LOOKUP(9.99E+307,MATCH(TRIM(LEFT(INDEX(total,MATCH($A$5,total1,0),0),2)),{"A","B","ac"},0),INDEX(total,MATCH($A$5,total1,0),0))))

* This formula was adapted from a version that initailly had to search for "A" or "VC" so I changed 3rd criteria to "ac" so it didn't record the "VC" entries

Without seeing some sample data it's hard to tell what you want the formula to do.

Maybe all you want to do is replace the formula blank like this:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"NO_DATA"....

On a side note...

I would replace both:

REPT("Z",255)
9.99E+307

With:

"zzzzz"
1E100

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,......

Sorry, but I have a personal rule where I won't open files with macros or that are bigger than 50kbs.

I just noticed that you're using Excel 2007.

You can replace all of this:

LOOKUP(REPT("Z",255),CHOOSE({1,2},"",

With the IFERROR function.

=IFERROR(LOOKUP(1E100,......),"No_Data")

stripped file - description would still apply

in the book4 workbook look in I3 and I4 for formula - in other file look in Last service parked in PRIORITY sheet

Ta Nigel

I don't think your current formula works because LEFT function is pulling the first two characters, that won't ever match with "A" unless the entry is "A" only. Try this version

=IFERROR(LOOKUP(2,1/(LEFT(INDEX(total,MATCH($A$5,total1,0),0))="A"),INDEX(total,MATCH($A$5,total1,0),0)),"NoData")