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

What If She's Not the Right One?

What If She's Not the Right One?