Match one out of several possibilities

Hello,
I'm looking for a formula that will return a zip code when looking up a county. Obviously, counties may have several zip codes within them, but I would like Excel to pick one randomly and match it to the county that it's in. I have a table with all zip codes and their associated counties.
Then I have an other table with people and the counties they live in. I want to match any zip code to them (regardless of what it truly is) that is in that county...
Thank you!
Caroline

Hi Caroline and welcome to the forum.

To do this in a single formula, you can use an array formula such as this:

=INDEX($B$2:$B$19,
    LARGE(IF($A$2:$A$19=$H2,ROW($A$2:$A$19)-ROW($A$2)+1),
        RANDBETWEEN(1,COUNTIF($A$2:$A$19,$H2))))

It will return a #NUM! error if the country is not in the lookup table.

Hi Caroline,

The LARGE() part is required. The reason it isn't working the way you wanted is because you hadn't entered it as an array formula. If you look at my attached example, you'll notice that the formulas in the formula bar are surrounded by parentheses { }. To get this effect, when you type the formula into the formula bar, to complete the entry, instead of just pressing the usual ENTER, you have to press CTRL+SHIFT+ENTER. Note that you can't enter the { } manually yourself - you have to let Excel do it for you.

I've attached your file with corrections. Yes, the formulae take a long time to calculate because you have 40,000+ rows of data.

Hi Caroline,

To mark your thread as solved, please click EDIT in your first post, click GO ADVANCED and set the PREFIX box to SOLVED.

You can tap on the scales in the top right corner of a member's post to give that member some kudos.

Thanks