Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #9  
Old 04-25-2018, 07:09 AM
ArviLaanemets ArviLaanemets is offline If or formula help Windows 8 If or formula help 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
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Copy Row 2 Row But Next Column In Formula From Another Tab TimG Excel 3 04-16-2018 09:20 PM
Help with Formula Ragincajun22 Excel 1 08-24-2017 08:44 AM
If or formula help Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
Need help with dragging a formula and changing a reference column as I drag the formula. LupeB Excel 1 10-22-2015 03:02 PM
Formula help furface00 Excel 10 02-26-2012 05:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:01 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft