Index function with wrapped match function problem

[Jtack1]

Ok,
First time posting in this forum. I tried my best to solve this via google and YouTube but was unable to find a solution, so here I am. I have a complex formula that needs to use a Index function and be wrapped in with a match function as well. as you can see in the image. There is a list of names in column A (A11:A29) and a table with Yes/no results for programming language that those people understand (E11:K29). In S17 I need the name of the programmer that knows all of them. In this case Jang, Ling in row 15. Using a index formula how can I get his name to appear in S17?

You can download the XLS HERE to see the setup. Sorry have no other way of uploading the Sheet at this time. Thank you for you help it is greatly appreciated.

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
9​	Consultant Specialist Table​
10​	Name​	Gender​	Age​	Yrs​	VB​	C#​	C++​	Java​	PHP​	ASP​	HTML 5​
11​	Bhua, Li	M​	21​	2​	N​	Y​	Y​	Y​	N​	N​	N​
12​	Kadafi, Kim	F​	22​	4​	Y​	Y​	N​	Y​	Y​	N​	N​
13​	Kidd, Laura	F​	23​	1​	Y​	N​	N​	Y​	Y​	Y​	Y​
14​	Sea, Tyler	M​	24​	3​	N​	N​	N​	Y​	Y​	Y​	N​
15​	Jang, Lin	F​	25​	5​	Y​	Y​	Y​	Y​	Y​	Y​	Y​
16​	White, Danzel	M​	25​	3​	Y​	Y​	N​	Y​	Y​	Y​	N​
17​	Chavez, Juanita	F​	26​	4​	Y​	N​	Y​	N​	Y​	N​	N​
18​	Naik, Chandana	F​	28​	9​	N​	N​	Y​	Y​	N​	N​	Y​
19​	Biagi, John	M​	29​	7​	Y​	N​	Y​	N​	Y​	N​	Y​
20​	Goldsmith, Ed	M​	31​	8​	Y​	Y​	N​	N​	N​	N​	Y​
21​	Hatt, Jerimiah	M​	32​	5​	N​	N​	Y​	Y​	Y​	N​	Y​
22​	Santos, Carlos	M​	32​	7​	N​	Y​	N​	N​	N​	N​	Y​
23​	Sosa, Flower	F​	32​	10​	Y​	Y​	Y​	y​	y​	Y​	Y​
24​	Dan, Jake	M​	34​	19​	Y​	N​	N​	N​	Y​	Y​	N​
25​	Harley, Jim	M​	38​	9​	N​	N​	N​	N​	Y​	Y​	Y​
26​	Diaz, Jorge	M​	38​	15​	Y​	Y​	Y​	Y​	Y​	Y​	N​
27​	Smith, Kylie	F​	45​	7​	Y​	Y​	Y​	N​	Y​	N​	N​
28​	Brown, Fred	M​	50​	13​	N​	N​	Y​	Y​	Y​	Y​	Y​
29​	Wongley, Susie	F​	65​	23​	Y​	N​	N​	Y​	Y​	Y​	N​

<tbody>
</tbody>

In N2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$11:$A$29,SMALL(IF(MMULT(($E$11:$K$29="Y")+0,
    TRANSPOSE(COLUMN($E$11:$K$29)^0))=COLUMNS($E$11:$K$29),
    ROW($A$11:$A$29)-ROW($A$11)+1),ROWS($N$2:N2))),"")

If you convert the range into a table, using Insert | Table...

In N2 control+shift+enter, not just enter, and copy down:

<strike></strike>=IFERROR(INDEX(Table1[Name],SMALL(IF(MMULT((Table1[[VB]:[HTML 5]]="Y")+0,
    TRANSPOSE(COLUMN(Table1[[VB]:[HTML 5]])^0))=COLUMNS(Table1[[VB]:[HTML 5]]),
    ROW(Table1[Name])-ROW(INDEX(Table1[Name],1,1))+1),ROWS($N$2:N2))),"")

Note. In order to post an Excel readable, small sample, on the board itself (not a link to an outside source), you would want to try one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.
<strike></strike>

 

[Jtack1]

Thank you for helping me out with the table, however I'm a little confused with the two formulas that have been produced. The current excel sheet is already in a table format. Additionally, I need the Match function to be within the =Index formula. I'm not sure I am on the right path...

 

[Aladin Akyurek]

Thank you for helping me out with the table, however I'm a little confused with the two formulas that have been produced. The current excel sheet is already in a table format. Additionally, I need the Match function to be within the =Index formula. I'm not sure I am on the right path...

~

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

9​

Consultant Specialist Table​

​

​

​

​

​

​

​

​

​

​

10​

Name​

Gender​

Age​

Yrs​

VB​

C#​

C++​

Java​

PHP​

ASP​

HTML 5​

Jang, Lin

11​

Bhua, Li

M​

21​

2​

N​

Y​

Y​

Y​

N​

N​

N​

Sosa, Flower

12​

Kadafi, Kim

F​

22​

4​

Y​

Y​

N​

Y​

Y​

N​

N​

13​

Kidd, Laura

F​

23​

1​

Y​

N​

N​

Y​

Y​

Y​

Y​

14​

Sea, Tyler

M​

24​

3​

N​

N​

N​

Y​

Y​

Y​

N​

15​

Jang, Lin

F​

25​

5​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

16​

White, Danzel

M​

25​

3​

Y​

Y​

N​

Y​

Y​

Y​

N​

17​

Chavez, Juanita

F​

26​

4​

Y​

N​

Y​

N​

Y​

N​

N​

18​

Naik, Chandana

F​

28​

9​

N​

N​

Y​

Y​

N​

N​

Y​

19​

Biagi, John

M​

29​

7​

Y​

N​

Y​

N​

Y​

N​

Y​

20​

Goldsmith, Ed

M​

31​

8​

Y​

Y​

N​

N​

N​

N​

Y​

21​

Hatt, Jerimiah

M​

32​

5​

N​

N​

Y​

Y​

Y​

N​

Y​

22​

Santos, Carlos

M​

32​

7​

N​

Y​

N​

N​

N​

N​

Y​

23​

Sosa, Flower

F​

32​

10​

Y​

Y​

Y​

y​

y​

Y​

Y​

24​

Dan, Jake

M​

34​

19​

Y​

N​

N​

N​

Y​

Y​

N​

25​

Harley, Jim

M​

38​

9​

N​

N​

N​

N​

Y​

Y​

Y​

26​

Diaz, Jorge

M​

38​

15​

Y​

Y​

Y​

Y​

Y​

Y​

N​

27​

Smith, Kylie

F​

45​

7​

Y​

Y​

Y​

N​

Y​

N​

N​

28​

Brown, Fred

M​

50​

13​

N​

N​

Y​

Y​

Y​

Y​

Y​

29​

Wongley, Susie

F​

65​

23​

Y​

N​

N​

Y​

Y​

Y​

N​

N2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Table1[Name],SMALL(IF(MMULT((Table1[[VB]:[HTML 5]]="Y")+0,TRANSPOSE(COLUMN(Table1[[VB]:[HTML 5]])^0))=COLUMNS(Table1[[VB]:[HTML 5]]),ROW(Table1[Name])-ROW(INDEX(Table1[Name],1,1))+1),ROWS($N$2:N2))),"")

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. When done properly, Excel puts a pair of { and } around the formula in recognition.

 

[Jtack1]

Cool, and thank you again that solution defiantly worked and the correct name came up in the destination cell. However, I do not believe
I will get credit for the question due to the fact that the match function is not in the formula itself. My book shows an example like the following. =INDEX(A9:J21, MATCH(Q10,A9:A21,0), 8) and again that is just a generic example. Does anyone know of a workaround using these to techniques?

 

[Aladin Akyurek]

Cool, and thank you again that solution defiantly worked and the correct name came up in the destination cell. However, I do not believe
I will get credit for the question due to the fact that the match function is not in the formula itself.

You are welcome. Is the potential authorship relevant here?

My book shows an example like the following. =INDEX(A9:J21, MATCH(Q10,A9:A21,0), 8) and again that is just a generic example. Does anyone know of a workaround using these to techniques?

I'm not getting the issue here. Are you expecting an INDEX\MATCH solution of some kind?

 

[Jtack1]

I do not believe there are any authorship issues. This in actually is a assignment for a accelerated excel course. The book that I'm referencing went over using a =Index wrapped match formula this is where I got the example. While that one I was able to understand this one is confusing me. The last question in this section asks specifically, "Use the MATCH function wrapped inside the the INDEX function as you did in the chapter to find the one agent who is proficient in all seven languages." (Microsoft Excel 2013 Complete, Freund, Jones, Stark. P399) So to answer your question I was looking for something similar to the example above. I've tried multiple times to input different values in a similar equation but was never able to get the correct result.
,

 

[Aladin Akyurek]

I do not believe there are any authorship issues. This in actually is a assignment for a accelerated excel course. The book that I'm referencing went over using a =Index wrapped match formula this is where I got the example. While that one I was able to understand this one is confusing me. The last question in this section asks specifically, "Use the MATCH function wrapped inside the the INDEX function as you did in the chapter to find the one agent who is proficient in all seven languages." (Microsoft Excel 2013 Complete, Freund, Jones, Stark. P399) So to answer your question I was looking for something similar to the example above. I've tried multiple times to input different values in a similar equation but was never able to get the correct result.
,

I'm curious how that book would go about it. They probably would extend the table with an additional range:

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

9​

Consultant Specialist Table​

10​

Name​

Gender​

Age​

Yrs​

VB​

C#​

C++​

Java​

PHP​

ASP​

HTML 5​

count​

Jang, Lin

11​

Bhua, Li

M​

21​

2​

N​

Y​

Y​

Y​

N​

N​

N​

3​

12​

Kadafi, Kim

F​

22​

4​

Y​

Y​

N​

Y​

Y​

N​

N​

4​

13​

Kidd, Laura

F​

23​

1​

Y​

N​

N​

Y​

Y​

Y​

Y​

5​

14​

Sea, Tyler

M​

24​

3​

N​

N​

N​

Y​

Y​

Y​

N​

3​

15​

Jang, Lin

F​

25​

5​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

7​

16​

White, Danzel

M​

25​

3​

Y​

Y​

N​

Y​

Y​

Y​

N​

5​

17​

Chavez, Juanita

F​

26​

4​

Y​

N​

Y​

N​

Y​

N​

N​

3​

18​

Naik, Chandana

F​

28​

9​

N​

N​

Y​

Y​

N​

N​

Y​

3​

19​

Biagi, John

M​

29​

7​

Y​

N​

Y​

N​

Y​

N​

Y​

4​

20​

Goldsmith, Ed

M​

31​

8​

Y​

Y​

N​

N​

N​

N​

Y​

3​

21​

Hatt, Jerimiah

M​

32​

5​

N​

N​

Y​

Y​

Y​

N​

Y​

4​

22​

Santos, Carlos

M​

32​

7​

N​

Y​

N​

N​

N​

N​

Y​

2​

23​

Sosa, Flower

F​

32​

10​

Y​

Y​

Y​

y​

y​

Y​

Y​

7​

24​

Dan, Jake

M​

34​

19​

Y​

N​

N​

N​

Y​

Y​

N​

3​

25​

Harley, Jim

M​

38​

9​

N​

N​

N​

N​

Y​

Y​

Y​

3​

26​

Diaz, Jorge

M​

38​

15​

Y​

Y​

Y​

Y​

Y​

Y​

N​

6​

27​

Smith, Kylie

F​

45​

7​

Y​

Y​

Y​

N​

Y​

N​

N​

4​

28​

Brown, Fred

M​

50​

13​

N​

N​

Y​

Y​

Y​

Y​

Y​

5​

29​

Wongley, Susie

F​

65​

23​

Y​

N​

N​

Y​

Y​

Y​

N​

4​

<tbody>
</tbody>

In L11 enter and copy down:

=COUNTIFS(Table1[@[VB]:[HTML 5]],"Y")

In N10 enter:

=INDEX(Table1[Name],MATCH(COUNTIFS(Table1[[#Headers],[VB]:[HTML 5]],"?*"),Table1[count],0))

As you see, we just get the first consultant (name) who has all. All others equally qualified cannot be gotten with INDEX\MATCH ("match inside index").

 

[Jtack1]

Well, I can't thank you enough. You have been extremely helpful. I pride myself on trying to figure this kind of thing out. This one stumped me totally. Thanks again!

 

[Aladin Akyurek]

Well, I can't thank you enough. You have been extremely helpful. I pride myself on trying to figure this kind of thing out. This one stumped me totally. Thanks again!

You are welcome.