Three tables, need to use two to populate data in third

Hi there -
The attached file is mostly self explanatory, but I need help answering the question "Does the driver speak language of country s/he drove in?" by using a table that shows the drivers and where they drove, then another table that shows drivers and the language(s) each speaks.

Thank you in advance for any help you can offer! :)
Drivers.xlsx
AnalyticsTeamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Use this array formula in cell D2 and copy down:

=IF(SUM(($C$10:$C$14=TRANSPOSE($B$17:$B$20))*(A2=$A$10:$A$14)*(C2=TRANSPOSE($A$17:$A$20)))>0,"Yes","No")

Enter by pressing CTRL+SHIFT+ENTER.

See attached.

Kevin
Q-26945210.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zack BarresseCEOCommented:
Can I ask you this, how much data do you have, or expect to have?  If you're looking at large amounts of data, I would recommend this go into an Access database.  You could write a simple query to return the results you're looking for there as well.

If you're not planning on moving this to Access, I would recommend separating the data a little more.  I don't know if this is your actual data or not.  I suspect it is not.  In any case, your data structure is going to become important, especially in larger quantities, so I recommend putting some thought into how you want this laid out.

@Kevin: heya!

Regards,
Zack
0
zorvek (Kevin Jones)ConsultantCommented:
Zack the firefighter paramedic dude!

Who needs Access when Excel can do anything Access can do and more? You don't like my array math? ;-)

Thank you again for the ride and lunch! Very much appreciated! Did you get to the car wash yet?

Smitty came by the other day with some wild stories about income sources and corporate greed. He also shared some of his recently harvested fruits and veggies.
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Zack BarresseCEOCommented:
Haha!  I hear ya about Excel.  It's certainly my personal preference!  And I love your array math! :)

I did get the car washed, yes.  It needs it again though!  Hard to do with the twins these days.  They're both teething.  Oh the joys.

Smitty does have good harvests!
0
zorvek (Kevin Jones)ConsultantCommented:
You could have a rummage/garage sale and make enough enough money to hire a sitter, drive the 150 miles to civilization, and dine at a swanky restaurant with the wifey...just with the crap in that car!
0
Zack BarresseCEOCommented:
LOL!!  It's closer to 200 miles to civilization.  ;)  I'm working on those income sources anyway.  :)
0
AnalyticsTeamAuthor Commented:
Thanks guys, so very much.
There is a larger amount of data (you were right it's not real data :) but it's doubtful that it will be put into an Access database although I will recommend it.

I do have a follow up question though...if I have a driver who speaks two languages, and a country that has multiple languages, will your array math still return the correct result? I can send over another example if you'd like.
0
zorvek (Kevin Jones)ConsultantCommented:
The formula will work and you already have a case for that: James Profows speaks two languages and drove through Canada which speaks two languages.

Kevin
0
AnalyticsTeamAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.