Possible to MATCH names in matrix layout?

Wanderer

New Member
Joined
Oct 14, 2002
Messages
17
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Wanderer

New Member
Joined
Oct 14, 2002
Messages
17
Yes, each lookup_value, in this instance each individual's name, is unique.

Thank you.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 14, 2002
Messages
17

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 14, 2002
Messages
17

ADVERTISEMENT

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

New Member
Joined
Oct 14, 2002
Messages
17
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCD
1BalanceNames1Names2
2100Brown,AnnBrown,Bill
390Jones,JimJones,Jan
480Ott,EricHauge,Ned
570Cash,JohnTrey,Julie
6
7
8Hauge,Ned8080
9
Sheet1


If you have performace problems using the above formulas, you can maybe use the following scheme...
aaLookupWithSumProd Wanderer.xls
ABCDEFGH
1BalanceNames1Names2Columnstohide
2100Brown,AnnBrown,BillHauge,Ned80#N/A3
390Jones,JimJones,Jan
480Ott,EricHauge,Ned
570Cash,JohnTrey,Julie
6
Sheet1


Aladin
 

Forum statistics

Threads
1,144,056
Messages
5,722,256
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top