Wednesday, June 5, 2013

Validate a list against another (checklist)

To check if a subset of values are contained in another subset of values.


=IF((IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(cell_reference," ",""),"-",""),validation_data,2,FALSE),"No"))="No","No","Yes")

substituting "No" with "O" and "Yes" with "P" and changing the font to Wingdings 2 (Microsoft) should return  

 

Substitute was used to remove white space as well as hyphens as the validation database was void of white space and hyphens.

Also the index / match can be used


(INDEX(target_result,MATCH(criteria,target_search_criteria,0))

Cheers!

No comments:

Post a Comment

Windows NT delete user completely

https://windowsreport.com/fix-signed-in-temporary-profile-windows-10/ HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Prof...