Hi, I've been mulling this formula problem for some time and I haven't been able solve it. I'm confident forum users can help me find the solution.

Goal: in a formula, concatenate every combination from multiple arrays of unequal size to be stored as a name range

There are two named range arrays (Key1 and Key2), values outlined below:

Key1 Key2
B 12
D 1Z
Q
P
M
U
Y

The end solution that I would like to be stored as a name range is:

Key_All
B12
B1Z
D12
D1Z
Q12
Q1Z
P12
P1Z
M12
M1Z
U12
U1Z
Y12
Y1Z

Here is the formula attempt I tried:

Attempt #1:
Formula: copy to clipboard 
=Key1&Key2


but, this just yields the following: {"B12","D1Z","N/A","N/A","N/A","N/A","N/A"}


Can this be accomplished WITHOUT using VBA? And WITHOUT having to create the resulting range of values in a worksheet? ---> basically, it would be ideal to have a formula create and store the result as a named range.

Thank you in advance!