yogi_Based On A Set Of Needs And Qualifications Tables Determine Whether Individuals Are Qualified

              Google Spreadsheet   Post  #1856

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-05-2014

post by  Allie Hopkins:
https://productforums.google.com/forum/#!mydiscussions/docs/jci6HbFHbeo
Compare arrays
I want to compare two different arrays to see if one is contained in the other.  Essentially, I have a sheet of Area Needs indicating what levels of certain key areas must be met.  I also have a skill sheet that contains users and their current levels of these same key areas.  I'd like to create a matrix on sheet Qualified that indicates if a person meets the criteria specified in the area needs.

I have shared a file as example.

https://docs.google.com/a/google.com/spreadsheets/d/1QEtEs2Vb5u-7qCzGOhu1k_DXREYWnsW6vy_rVD97i0k/edit?usp=sharing

Can this be done with a combination of standard formulas or should I write a custom function?

---

Using my examples in the shared sheet:

Fred is a tech1 (from Ldaps)

Fred has taken training1 and training2 in ABC (from Skills)

Area A needs a tech1 to take training 1 in ABC (from AreaNeeds)

Therefore, Fred is qualified in Area A and should show TRUE on Qualified sheet.

What I have done thus far is grabbed an array of the skills of a given user using a query and another array of area needs using another query.  I'm trying to compare these two arrays to see if one contains all the items in another.  This does not work the way I've done it since the vlookup in the formula is only looking at the first column of the query results and not the other two:

countif(iferror(ARRAYFORMULA(VLOOKUP(query(AreaNeeds!$A$1:$E$82, "select C,D,E where A = '"&B$1&"' and B = '"&vlookup($A4,Ldaps!$A$2:$B,2,false)&"' label C '', D '', E ''", -1),query(Skills!$A$1:$E$102, "select B,C,D where A = '"&$A4&"' label B '', C '', D ''", -1),1,false)),"Not"),"Not")

-----------------------------------------------------------------------------------------------------------------