Match & Index Combined Row & Multiple extractions

[Ronald Cayne]

I am looking up a text string in a1 and seeing if I have a match in a row
For example row B EXAMPLE If I have a match in B2-Z25 I use the index
command to exract a value from within the row AA2 to AX2. No problem so
far! This I can do.However what happens if the same text string turns up in
B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using
the index function values in the range AA2-AX2 and combine their values(ie
get their sum).


Maybe there is another way.


What I am doing is looking up a fund name in A1. Clients names are in
Column A. If a client holds the fund I extract the number of units he holds
by using the index command to pull the number of units he holds for that
fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units
held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
in Fund 1. If the fund in a1 turns up more than once for a client the
problem is to determine his total number of units he holds for that fund

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.

There's also an alternative to this using SUMPRODUCT at:
http://www.bygsoftware.com/Excel/functions/sumproduct.htm

(and please don't cross post ;-)
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Ronald Cayne]

I think I don;t quite understand this!.

Clients are in column A. Eg. Products in B2-Z26. Units held in products are
held in Columns Eg. AA2-AX2. where AA2 contains the number of units held by
under product 1 etc. If Client holds more than 1 of the same product. eg. XXX
Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of
units held in this fund?? That is the problem . I do not have a problem in
establishing the amount of units on the first match of the fund lookup in A1.
The problem occurs if the same fund is held by the same client eg Product G2 or
G3 which may have been acquired at a different point in time.

Clear as mud !

 

[Alan Beban]

Unless I misunderstand the exercise (a small illustration, say 3 rows
and 9 columns, would have been immensely helpful), with the client name
in BA1 and the product name in BB1, then unless I made an error in
converting the formula in my small test range to the one needed in your
large stated range

=SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))

By the way, you stated 25 columns of products and 24 columns of units.

Alan Beban

 

[Harlan Grove]

=SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"
&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"
&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))

....

One problem: if BA1 matched the value in cell A2, then MATCH would return 1,
making the formula equivalent to

=SUMIF(B1:Z1,BB1,AA1:AX1)

Looks like an off-by-one index bug.

Aside from this, if the formula above does what the OP wants, so would

=SUMIF(INDEX(B2:Z26,MATCH(BA1,A2:A26,0),0),BB1,
INDEX(AA2:AY26,MATCH(BA1,A2:A26,0),0))

 

[Ronald Cayne]

Example file is at the following location. I had trouble uploading a file here

ftp://guest.crescentauctions.com:[email protected]/

The file name is test_file


Many thanks Alan for your help. If you can solve my problem using the indirect command would you kindly explain how it is working for
this solutione.



Regards

Ron

 

[Ronald Cayne]

The ftp address should have been:

ftp://guest.crescentauctions.com:[email protected]

 

[Ronald Cayne]

Please see a sample file at the following ftp site:

ftp://guest.crescentauctions.com:[email protected]

I really want to understand the solution to this problem

Ron