Check if ALL Row Values Exist In Another Column

[excel-help]

If I want to check if the value in a single cell (for example: E5) can also be found in a column of cells (for example: the named range "match_list"), I believe I can do this with either of the following two formulas:

=NOT(ISERROR(MATCH(E5,match_list,0)))
=IF(ISNA(VLOOKUP(E5,match_list,1,FALSE)),"FALSE","TRUE")

But is there a way to check to see if every value in a range of cells (for example: E5:AR5) can be found in the "match_list" range? So it would be FALSE if any one of the values in E5-AR5 can't be found in the match_list range, and it would be TRUE only if every one can be found in the match_list range?

Thanks!

[excel-help]

I'm not sure if it's the most elegant formula in the world, but this seems to work for me:

=SUMPRODUCT(--(ISNA(MATCH(E5:AR5,match_list2,0))))=COLUMNS(E5:AR5)

[GeoffW283]

Another possibility:

=SUM(--ISERROR(MATCH(E5:AR5,match_list,0)))=0

This is an array formula. Commit using CTRL-SHIFT-ENTER

[GeoffW283]

And another possibility similar to the above but a regular (not an array) formula:

=SUMPRODUCT(--ISERROR(MATCH(E5:AR5,match_list,0)))=0

[excel-help]

Thank you! I ultimately went with mine. I don’t know if it’s any better or worse, but it’s ever so slightly shorter 😊. I had to modify it a bit though (as best as I know how), because the formula seemed to break down if any of the cells in the E5:AR5 range were left blank (which they often will be).

I ultimately went with this:
=SUMPRODUCT(--(ISNA(MATCH(E5:AR5,match_list,0))))=0+COUNTBLANK(E5:AR5)

This also works:
=SUMPRODUCT(--ISERROR(MATCH(E5:AR5,match_list,0)))=0+COUNTBLANK(E5:AR5)

Thanks again. I will mark the thread as solved.

[GeoffW283]

So if it's a competition for shortest then I have (dealing with blanks):

Please Login or Register  to view this content.

Or, if you add zero to the match list, then that takes care of blanks and any of the above (shorter) formulas will work without further change.

Sorry - couldn't resist

[excel-help]

Well now you just crushed me… I’m switching to that

(Can’t simply add the zero because I’m not the only one who will edit the match list data.)