kodako: Validate a list against another (checklist)

Wednesday, June 5, 2013

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!

kodako