If #REF then load to Query If #VALUE then don't. Need help with loading #REF records

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
2019
Platform
Windows
Hello, Can you please help me find a workaround to load needed records

Is there a way to replace #REF error, but keep #VALUE one ?
Or in other way, Is there a way to load #REF records , but exclude #VALUE ones ?

If it helps I use the following formula in order to find keywords. And result with #REF means keywords was found. #VALUE means it wasn't.
Code:
=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,E14))*ROW(list)))
I don't know if SQL, VBA, M may help, but I can also try them if no simpler solution available.
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
2019
Platform
Windows
I found a bit easier to work with formula, so I would say this topic is closed, unless somebody wants to treat it as a challenge.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,914
Messages
5,411,202
Members
403,349
Latest member
Nikojoer

This Week's Hot Topics

Top