index and match

[LadyMo]

I have been sitting for hours trying to figure out the formula for one of the entries on my homework!! I NEED HELP BAD!!! What is the index-match formula to get the name of the consultant who knows all of the languages? Thanks in advance!!

[TABLE="width: 1290"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD="colspan: 11, align: center"]Business Analyst Table[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4, align: center"]Summary:[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Gender[/TD]
[TD]Age[/TD]
[TD]Yrs[/TD]
[TD]Java[/TD]
[TD]Python[/TD]
[TD]C#[/TD]
[TD]R[/TD]
[TD]PHP[/TD]
[TD]Java- Script[/TD]
[TD]Ruby[/TD]
[TD]Count[/TD]
[TD][/TD]
[TD="colspan: 3"]Total count of women[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Bhua, Li[/TD]
[TD]M[/TD]
[TD]21[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="colspan: 2"]Total count of men[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Kadafi, Kim[/TD]
[TD]M[/TD]
[TD]22[/TD]
[TD]4[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]4[/TD]
[TD][/TD]
[TD="colspan: 3"]Average age of women[/TD]
[TD="align: right"]29.83333[/TD]
[/TR]
[TR]
[TD]Kidd, Laura[/TD]
[TD]F[/TD]
[TD]23[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]5[/TD]
[TD][/TD]
[TD="colspan: 2"]Average age of men[/TD]
[TD][/TD]
[TD="align: right"]31.33333[/TD]
[/TR]
[TR]
[TD]Sea, Tyler[/TD]
[TD]M[/TD]
[TD]24[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="colspan: 3"]Median age of all consultants[/TD]
[TD="align: right"]30.58333[/TD]
[/TR]
[TR]
[TD]Jang, Lin[/TD]
[TD]F[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]7[/TD]
[TD][/TD]
[TD="colspan: 3"]Average years of experience[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]White, Danzel[/TD]
[TD]M[/TD]
[TD]25[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]5[/TD]
[TD][/TD]
[TD="colspan: 3"]Total years of Java experience (all consultants)[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Chavez, Juanita[/TD]
[TD]F[/TD]
[TD]26[/TD]
[TD]4[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="colspan: 3"]Consultant who knows all languages[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Naik, Chandana[/TD]
[TD]F[/TD]
[TD]28[/TD]
[TD]9[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Biagi, John[/TD]
[TD]M[/TD]
[TD]29[/TD]
[TD]11[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Goldsmith, Ed[/TD]
[TD]M[/TD]
[TD]31[/TD]
[TD]8[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hatt, Jerimiah[/TD]
[TD]M[/TD]
[TD]32[/TD]
[TD]5[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Santos, Carlos[/TD]
[TD]M[/TD]
[TD]32[/TD]
[TD]7[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sosa, Flower[/TD]
[TD]F[/TD]
[TD]32[/TD]
[TD]10[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan, Jake[/TD]
[TD]M[/TD]
[TD]34[/TD]
[TD]19[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harley, Jim[/TD]
[TD]M[/TD]
[TD]38[/TD]
[TD]9[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Diaz, Jorge[/TD]
[TD]M[/TD]
[TD]38[/TD]
[TD]15[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith, Kylie[/TD]
[TD]F[/TD]
[TD]45[/TD]
[TD]7[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brown, Fred[/TD]
[TD]M[/TD]
[TD]50[/TD]
[TD]13[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

If you want to learn the material, try reading this first: https://contexturesblog.com/archives/2012/07/12/check-multiple-criteria-with-excel-index-and-match/

 

[Fluff]

Here's another good site to look at https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/
Also what happens if you have 2 or more people who know all languages?

 

[wilsjohn]

INDEX($A$2:$L$20,MATCH(7,$L$2:$L$20,0),1)

Assuming the cell with "NAME" is in A2 this will work.

Looks up who knows 7 languages hence "7". and looks in column 1.

 

[James006]

Hire Jang, Lin ... right away ...!!!