Validate two words from cell using two other columns Microsoft Excel

[dskribow]

I've got what's probably a simple question concerning Microsoft Excel 2013 that I just can't figure out the answer to.
I have cell A1 that contains a name in the form of two words, such as "Jane Doe".
In two separate columns, I have separate lists of first and last names.
Here's what it looks like:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
</code>

	A	B	C
1	Jane Dooey	Jane	Doe
2		Jane	Dooey
3		John	Dooey
4		John	Doe

<tbody>
</tbody>

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
</code>
Here's what I'm trying to do: I'm trying to set up a validation rule in A1 that dissalows entering a two -word name that doesn't match a combination of columns B and C on some row.
How can I accomplish this?
Any and all help is much appreciated.

 

[AlphaFrog]

Try this...
=SUMPRODUCT(1*(($B$1:$B$4&" "&$C$1:$C$4)=A1))

Or this...
=MATCH(A1,INDEX($B$1:$B$4&" "&$C$1:$C$4,),0)

 

[Peter_SSs]

Not saying anything is wrong with the previous suggestion but I think in Data Validation, you should only need:

=MATCH(A1,$B$1:$B$4&" "&$C$1:$C$4,0)

 

[dskribow]

AlphaFrog, this is amazing. Thanks. I'm using the second equation.

I don't really understand why this works, though, and why =MATCH(A1,$B$1:$B$4&" "&$C$1:$C$4,0) wouldn't/doesn't work as well.

Thanks, again.

 

[AlphaFrog]

You're welcome.

This does work as well.
=MATCH(A1,$B$1:$B$4&" "&$C$1:$C$4,0)

 

[Peter_SSs]

... why =MATCH(A1,$B$1:$B$4&" "&$C$1:$C$4,0) wouldn't/doesn't work as well.

This will not work directly as a cell formula, but it should work (does for me and AlphFrog) if used as a Data Validation (Custom) formula.

 

[dskribow]

Thanks for the prompt replies AlphaFrog.

The formula: =MATCH(A1,$B$1:$B$4&" "&$C$1:$C$4,0) doesn't work for me like yours. I get a #VALUE error.

Do you have an idea as to why this is?

 

[dskribow]

This will not work directly as a cell formula, but it should work (does for me and AlphFrog) if used as a Data Validation (Custom) formula.

Thanks for the explanation, Peter_SSs. This makes more sense now.

You guys are the best!

 

[Peter_SSs]

The formula: =MATCH(A1,$B$1:$B$4&" "&$C$1:$C$4,0) doesn't work for me like yours. I get a #VALUE error.

Do you have an idea as to why this is?

In a cell, if you confirm its entry with Ctrl+Shift+Enter, not just enter then it should return a number, like AlphaFrog's formula (assuming the name is in the list).
data Validation automatically treats formulas as array formulas. That's the difference.

 

[dskribow]

Ok, that makes more sense. Thank you, Peter_SSs.