# Thread: Return value if there is an #N/A error Thanks: 0 Likes: 0

1. ## Return value if there is an #N/A error

Okay so I have a list of numbers that will be pasted next to an already placed VLOOKUP function, this VLOOKUP will lookup the numbers pasted in another list. This means the VLOOKUP returns a list of all the numbers that already exist in the 'lookup list' and then #N/A if a new number is there. I want a formula that returns those new numbers... for example

'Lookup list' Pasted VLOOKUP New (formula I'm looking for)
#245 #245 #245 #233
#267 #233 #N/A #288
#244 #267 #267
#235 #244 #244
#266 #288 #N/A
#235 #235
#266 #266

Thanks,
Matt

2. ## Re: Return value if there is an #N/A error

'Lookup list' Pasted VLOOKUP New (formula I'm looking for)
#245#245#245#233
#267#233 #N/A #288
#244 #267#267
#235 #244#244
#266 #288 #N/A
#235#235
#266#266

The spaces aren't showing in the original query

3. ## Re: Return value if there is an #N/A error

 'Lookup list' Pasted VLOOKUP New (formula needed) #245 #245 #245 #233 #267 #233 #N/A #288 #244 #267 #267 #235 #244 #244 #266 #288 #N/A #235 #235 #266 #266
Apologies, should have done a table to start.

4. ## Re: Return value if there is an #N/A error

Try this, copied down.

new numbers

 A B C D 1 2 #245 #245 #245 #233 3 #267 #233 #N/A #288 4 #244 #267 #267 5 #235 #244 #244 6 #266 #288 #N/A 7 #235 #235 8 #266 #266 9

 Cell Formula D2 =IFERROR(INDEX(B\$2:B\$10,AGGREGATE(15,6,(ROW(B\$2:B\$10)-ROW(B\$2)+1)/ISNA(C\$2:C\$10),ROWS(D\$2:D2))),"")

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Return value if there is an #N/A error

That is amazing! Thank you

6. ## Re: Return value if there is an #N/A error

Originally Posted by mattbnorris
That is amazing! Thank you
Glad it worked for you. Thanks for the follow-up.