I download each morning off the web information that is download as an
Excel document. Well right now, I am trying to get the "Index and
match" function to work. It works on the very first occurrence of an
item, but I cannot seem to figure out how to make it work in every
occurrence. Sometimes my first criteria will not even appear on that
day's information, and sometimes there could be as many as five
appearances. Therefore, what I am trying to do in my "Index and
Match" formula is locate the first occurrence and my first, and then
if any others appear extract them as well. Now they will always
appear, if they appear for that on line 14, of the download.

He is the formula that I am presently using:

{=INDEX('[CBHI-0129.xls]Sheet1'!$L$2:$L$100,MATCH(1,($I
$27='[CBHI-0129.xls]Sheet1'!$J$2:$J
$100)*("Data"='[CBHI-0129.xls]Sheet1'!$H$2:$H$100),0))}

Where I27 is what I am trying to match to in CBHI-0129, and this is
what could occur or could not occur, each day. In CBHI-0129 column H,
there can be the word Total or Data, all intermingled.

Example for my above post:
A B C D E F G H I J K
L M
Line 14 Data 301 $19,201.28
Line 15 Data 301
547.98
Line 16 Total 301
$19,749.26

OR

Line 14 Data 451 $24,951.65
Line 15 Total 451
$24,951.65

Hopes this example helps you in understanding what I am trying to do.



On Feb 3, 2:40*pm, caldog wrote:
I download each morning off the web information that is download as an
Excel document. *Well right now, I am trying to get the "Index and
match" function to work. *It works on the very first occurrence of an
item, but I cannot seem to figure out how to make it work in every
occurrence. *Sometimes my first criteria will not even appear on that
day's information, and sometimes there could be as many as five
appearances. *Therefore, what I am trying to do in my "Index and
Match" formula is locate the first occurrence and my first, and then
if any others appear extract them as well. *Now they will always
appear, if they appear for that on line 14, of the download.

He is the formula that I am presently using:

{=INDEX('[CBHI-0129.xls]Sheet1'!$L$2:$L$100,MATCH(1,($I
$27='[CBHI-0129.xls]Sheet1'!$J$2:$J
$100)*("Data"='[CBHI-0129.xls]Sheet1'!$H$2:$H$100),0))}

Where I27 is what I am trying to match to in CBHI-0129, and this is
what could occur or could not occur, each day. *In CBHI-0129 column H,
there can be the word Total or Data, all intermingled.

Try these array formulae
For sorted data :
=IF(ROWS($1:1)<=COUNTIF(J$2:J$10,C$1),INDEX(L$2:L$ 10,MATCH(1,(J$2:J$10=$C$1)*(H2:H10="data"),0)+ROWS ($1:1)-1),"")

For unsorted data
=IF(ROWS($1:1)<=COUNTIF(J$2:J$10,C$1),INDEX(L$2:L$ 10,SMALL(IF((J$2:J$10=C$1)*($H$2:$H$10="data"),ROW (H$2:H$10)-MIN(ROW(H$2:H$10))+1),ROWS($1:1))),"")

where C1= your I27
and change the range that fits to you. The column names are not changed.
Hope this is what you need.
With regards
Sreedhar
"caldog" wrote:

I download each morning off the web information that is download as an
Excel document. Well right now, I am trying to get the "Index and
match" function to work. It works on the very first occurrence of an
item, but I cannot seem to figure out how to make it work in every
occurrence. Sometimes my first criteria will not even appear on that
day's information, and sometimes there could be as many as five
appearances. Therefore, what I am trying to do in my "Index and
Match" formula is locate the first occurrence and my first, and then
if any others appear extract them as well. Now they will always
appear, if they appear for that on line 14, of the download.

He is the formula that I am presently using:

{=INDEX('[CBHI-0129.xls]Sheet1'!$L$2:$L$100,MATCH(1,($I
$27='[CBHI-0129.xls]Sheet1'!$J$2:$J
$100)*("Data"='[CBHI-0129.xls]Sheet1'!$H$2:$H$100),0))}

Where I27 is what I am trying to match to in CBHI-0129, and this is
what could occur or could not occur, each day. In CBHI-0129 column H,
there can be the word Total or Data, all intermingled.

Thanks for the reply I am trying the formula right now and will let
you know the results. BTW I have never used or seen used the [small]
command. Just so I know for my future use what is this command used
for?

Sreedhar,

It found the first occurance, but could not find the second occurance
in my example. For the second line it showed the same dollar amount
as what appears on the first line.

It is working now, except that it is finding all occurences of 301.
The two rows of "Data", $19,201.28, $547.98, and "Total" amount
$19,749.26.

What I am needing is for it to find all of the dollar amounts
associated with the word "Data" and the Code # "301". When it finds
the word "Total" that is when I need the formula to ignore the amount
in column L of my example.

Thanks again for the code.