View Single Post
 
Old 04-25-2018, 07:09 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 949
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

When the list of values to check is not too long, and you don't foresee a need to edit formula in future:
Code:
=IF(ISERROR(MATCH(A2,{"R102";"8500";"8501";"8502";"68"};0)),"","NSA")
Otherwise enter the list of check values into single-column table (may be on another - even hidden - sheet), and define the list range as Name. E.g. CheckVal. Now the formula will be:
Code:
=IF(ISERROR(MATCH(A2,CheckVal,0)),"","NSA")
and the length of check list is limited only by possible number of worksheet rows.

I'm not sure about array delimiter in 1st formula for US setings (is it comma or semicolon) - you have to check it yourself!

In case you need to return different values for different check lists, then VLOOKUP() is better solution.
Enter check list values and according return values into 2-column table (e.g. CheckValue, ReturnValue), and define table's datarange as Named Range (e.g. nCheckTable), or define the table as Table (Insert > Table), e.g. tCheckTable
The formula will be:
Code:
=IFERROR(VLOOKUP(A2,nCheckTable,2,0)),"")
or
Code:
=IFERROR(VLOOKUP(A2,tCheckTable,2,0)),"")
Reply With Quote