Hello. I'm a first time poster and have been very impressed with the knowledge base and responsiveness of the users of this forum. However, I have a problem that I have not seen solved in the archives.
I have a matrix of names listed in 200+ rows and each row occupies a varying number of columns (B,C,D,E,F,G) depending upon the number of registered family members. Column A contains the balance available to a particular family. <I'll try to type an abbreviated example.>
(A)Balance__(Col.B)______(Col.C)_____ . . .
100_________Brown, Ann___Brown, Bill . . . .
90__________Jones, Jim___Jones, Jan. . . .
80__________Ott, Eric____Hauge, Ned__ . . .
70__________Cash, John___Trey, Julie . . .
I am trying to MATCH a particular name and then INDEX the balance column (e.g. "Ott, Ned" returns 80). So, I have tried various MATCH arguments nested within an INDEX function. Unfortunately, MATCH only searches one column for the desired name, though I need to search for a match in (up to) six columns. Also, due to remarriages not everyone has the same last name, so I can't just list one-common family name in Col.B and INDEX it to Col.A.
My INDEX function is fine, but my interior MATCH sub-function is driving me nuts. I have tried nested IF functions for MATCH to search progressive columns if the name does not appear (i.e. if #N/A is returned, then try to return MATCH in ColumnX+1), but have had no success. Oh, and I am setting the match_type=0 to identify the exact value, so that is not the problem.
I would hope Excel is capable of searching and MATCHing a value/text in an entire matrix, but is the program only capable of MATCHing in one column?
Thank you in advance; and thanks for providing this forum -- it's an incredible resource.
I have a matrix of names listed in 200+ rows and each row occupies a varying number of columns (B,C,D,E,F,G) depending upon the number of registered family members. Column A contains the balance available to a particular family. <I'll try to type an abbreviated example.>
(A)Balance__(Col.B)______(Col.C)_____ . . .
100_________Brown, Ann___Brown, Bill . . . .
90__________Jones, Jim___Jones, Jan. . . .
80__________Ott, Eric____Hauge, Ned__ . . .
70__________Cash, John___Trey, Julie . . .
I am trying to MATCH a particular name and then INDEX the balance column (e.g. "Ott, Ned" returns 80). So, I have tried various MATCH arguments nested within an INDEX function. Unfortunately, MATCH only searches one column for the desired name, though I need to search for a match in (up to) six columns. Also, due to remarriages not everyone has the same last name, so I can't just list one-common family name in Col.B and INDEX it to Col.A.
My INDEX function is fine, but my interior MATCH sub-function is driving me nuts. I have tried nested IF functions for MATCH to search progressive columns if the name does not appear (i.e. if #N/A is returned, then try to return MATCH in ColumnX+1), but have had no success. Oh, and I am setting the match_type=0 to identify the exact value, so that is not the problem.
I would hope Excel is capable of searching and MATCHing a value/text in an entire matrix, but is the program only capable of MATCHing in one column?
Thank you in advance; and thanks for providing this forum -- it's an incredible resource.