Possible to MATCH names in matrix layout?

[Wanderer]

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.

 

[Aladin Akyurek]

Is the lookup value unique in the "matrix", that's, occurs once?

 

[Wanderer]

Yes, each lookup_value, in this instance each individual's name, is unique.

Thank you.

 

[Aladin Akyurek]

Yes, each lookup_value, in this instance each individual's name, is unique.

Let A1:C5 house the following sample...

{"Balance","Names1","Names2";
100,"Brown, Ann","Brown, Bill";
90,"Jones, Jim","Jones, Jan";
80,"Ott, Eric","Hauge, Ned";
70,"Cash, John","Trey, Julie"}

=INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$C$5=A8)*(ROW($B$2:$C$5)))-CELL("Row",$A$1:$C$5))

will give you the balance associated with the name (the lookup value) A8 houses.

This would give you a #VALUE! error if the lookup value does not occur in your data. If you're willing to pay some additional price for performance, you can use...

=IF(COUNTIF($B$2:$C$5,A8),INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$C$5=A8)*(ROW($B$2:$C$5)))-CELL("Row",$A$1:$C$5)),"Not Available")

Postscript. Given the uniqueness of the lookup value, the following much less expensive formula would also work...

=SUMIF($B$2:$C$5,A8,$A$2:$A$5)

with A8 housing the name to look up.

Aladin
This message was edited by Aladin Akyurek on 2002-10-15 05:38

 

[Wanderer]

Unfortunately, the first equation does not work and the second is expensive and problematic.

The SUMIF function is certainly more elegant than the first two suggestions, but the SUMIF refuses to evaluate a range greater than the first column stated in the SUMIF(range argument, . . , . . ).
For instance, if I write:
=SUMIF($B$2:$C$5, A8, $A$2:$A$5),
then the equation only returns the respective balance for A8 if the name written A8 is located in Column B. If the name in A8 is located in Column C, then "0" is returned.
However, if I alter the range argument in SUMIF to:
=SUMIF($C$2:$C$5, A8, $A$2:$A$5), then the correct balance is returned for the specified name if it resides in Col. C, but Col. B is omitted from the search.

So, is the SUMIF function capable of evaluating ranges with multiple rows AND multiple columns; that is, is it capable of searching a matrix and not just the first column of a matrix.

Aladin thanks for all your help, and thanks to whoever can illumniate myself.
This message was edited by Wanderer on 2002-10-15 19:20

 

[Aladin Akyurek]

On 2002-10-15 19:03, Wanderer wrote:
Unfortunately, the first equation does not work and the second is expensive and problematic.

Well, the first formula is also the main part of the second which becomes more expensive by the COUNTIF test. When the second's result is a hit, the first should also produce the same result.

The SUMIF function is certainly more elegant than the first two suggestions, but the SUMIF refuses to evaluate a range greater than the first column stated in the SUMIF(range argument, . . , . . ).
For instance, if I write:
=SUMIF($B$2:$C$5, A8, $A$2:$A$5),
then the equation only returns the respective balance for A8 if the name written A8 is located in Column B. If the name in A8 is located in Column C, then "0" is returned.
However, if I alter the range argument in SUMIF to:
=SUMIF($C$2:$C$5, A8, $A$2:$A$5), then the correct balance is returned for the specified name if it resides in Col. C, but Col. B is omitted from the search.

So, is the SUMIF function capable of evaluating ranges with multiple rows AND multiple columns; that is, is it capable of searching a matrix and not just the first column of a matrix.

That's right. I goofed there.

How many columns do you have?

 

[Wanderer]

I will have seven columns for names (Columns B,C,D,E,F,G,H) with Column A containing each respective family's balance. However, there are only a few instances where a large family will occupy all seven columns. Most often there will only be two or three names for smaller families, so Columns E,F,G,H, will be empty.

I was unclear to your last response. Is the SUMIF array restriced to the first column listed?

I will try your first/second suggestion again.

I truly appreciate the effort, thanks.

 

[Wanderer]

Your second solution worked after I adjusted my matrix. Thank you very much.

If you still have any suggestions regarding SUMIF evaluating<range> over an entire matrix, I would be eager to learn. Thanks, again.
This message was edited by Wanderer on 2002-10-15 21:49

 

[Aladin Akyurek]

On 2002-10-15 21:47, Wanderer wrote:
Your second solution worked after I adjusted my matrix. Thank you very much.

If you still have any suggestions regarding SUMIF evaluating<range> over an entire matrix, I would be eager to learn. Thanks, again.
This message was edited by Wanderer on 2002-10-15 21:49

Here is the SUMPRODUCT solution...

aaLookupWithSumProd Wanderer.xls
	A	B	C	D
1	Balance	Names1	Names2
2	100	Brown,Ann	Brown,Bill
3	90	Jones,Jim	Jones,Jan
4	80	Ott,Eric	Hauge,Ned
5	70	Cash,John	Trey,Julie
6
7
8	Hauge,Ned	80	80
9
Sheet1

If you have performace problems using the above formulas, you can maybe use the following scheme...

aaLookupWithSumProd Wanderer.xls
	A	B	C	D	E	F	G	H
1	Balance	Names1	Names2				Columnstohide
2	100	Brown,Ann	Brown,Bill		Hauge,Ned	80	#N/A	3
3	90	Jones,Jim	Jones,Jan
4	80	Ott,Eric	Hauge,Ned
5	70	Cash,John	Trey,Julie
6
Sheet1

Aladin