Hello All,

This is on Excel 2007
I would like to see if the array formula below can be changed to check for
another criteria.
Want this currently does is finds all occurances of cell AL527(despatch
note) and matches that with the sheet "Data" col N (despatch note) and the
result is taken from sheet "Data" col E (qty). If no match found then the
result is ""
The reason the ROW 3 at the end has $ is it helps with copying the formula
as I am using it for Columns rather than rows, I could not find a formula
that worked for colums.

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$ 1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3)) ),"")}


I now want to find all occurances of cell AL527 as before but only if the
value in cell BR527 is matched with a value from sheet "Data" col S

This array formula is in cell BR527

AL527(despatch note), Col N (despatch note), Col S (period)

{=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDE X(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0) ))}


Is it possible to combine the 2 formula's together?
I have been trying but cant even get a formula to be accepted :-(

Thanks
Winnie

Attempting to summarise:

Your first formula takes a value from column E at the same row as the third
occurrence of AL527 within column N or returns blank if there is no third
occurrence; and your second formula takes a value from column S at the same
row as the first occurrence of AL527 within column N or returns blank if it
doesn't occur.

So, you want a single formula that takes a value from column E at the same
row as the third occurrence of AL527 within column N, but only if... <and
then the summary kind of falls apart, because the first could only work if
the second were true anyway

Can you phrase your overall requirements in a similar sentence?




"winnie123" wrote in message
...
Hello All,

This is on Excel 2007
I would like to see if the array formula below can be changed to check for
another criteria.
Want this currently does is finds all occurances of cell AL527(despatch
note) and matches that with the sheet "Data" col N (despatch note) and the
result is taken from sheet "Data" col E (qty). If no match found then the
result is ""
The reason the ROW 3 at the end has $ is it helps with copying the formula
as I am using it for Columns rather than rows, I could not find a formula
that worked for colums.

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$ 1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3)) ),"")}


I now want to find all occurances of cell AL527 as before but only if the
value in cell BR527 is matched with a value from sheet "Data" col S

This array formula is in cell BR527

AL527(despatch note), Col N (despatch note), Col S (period)

{=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDE X(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0) ))}


Is it possible to combine the 2 formula's together?
I have been trying but cant even get a formula to be accepted :-(

Thanks
Winnie

Sorry Steve,

It was late last night when I posted this.

I only want to do the calculation if on the sheet "Data" Column S has the
same value as Column BR on my other sheet

If BR257 = "Data" Column S(whatever row that is in then

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$ 1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3)) ),"")}

Does that make anymore sense?
Winnie

"Steve Dunn" wrote:

Attempting to summarise:

Your first formula takes a value from column E at the same row as the third
occurrence of AL527 within column N or returns blank if there is no third
occurrence; and your second formula takes a value from column S at the same
row as the first occurrence of AL527 within column N or returns blank if it
doesn't occur.

So, you want a single formula that takes a value from column E at the same
row as the third occurrence of AL527 within column N, but only if... <and
then the summary kind of falls apart, because the first could only work if
the second were true anyway

Can you phrase your overall requirements in a similar sentence?




"winnie123" wrote in message
...
Hello All,

This is on Excel 2007
I would like to see if the array formula below can be changed to check for
another criteria.
Want this currently does is finds all occurances of cell AL527(despatch
note) and matches that with the sheet "Data" col N (despatch note) and the
result is taken from sheet "Data" col E (qty). If no match found then the
result is ""
The reason the ROW 3 at the end has $ is it helps with copying the formula
as I am using it for Columns rather than rows, I could not find a formula
that worked for colums.

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$ 1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3)) ),"")}


I now want to find all occurances of cell AL527 as before but only if the
value in cell BR527 is matched with a value from sheet "Data" col S

This array formula is in cell BR527

AL527(despatch note), Col N (despatch note), Col S (period)

{=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDE X(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0) ))}


Is it possible to combine the 2 formula's together?
I have been trying but cant even get a formula to be accepted :-(

Thanks
Winnie

I'm reluctant to give this formula, because I still think clarification is
required, but this matches what you've requested:

=IFERROR(IF(MATCH($BR$257,Data!$S$1:$S$500,0),INDE X(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL52 7,ROW(Data!$N$1:$N$500)),3)))),"")



"winnie123" wrote in message
...
Sorry Steve,

It was late last night when I posted this.

I only want to do the calculation if on the sheet "Data" Column S has the
same value as Column BR on my other sheet

If BR257 = "Data" Column S(whatever row that is in then

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$ 1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3)) ),"")}

Does that make anymore sense?
Winnie

"Steve Dunn" wrote:

Attempting to summarise:

Your first formula takes a value from column E at the same row as the
third
occurrence of AL527 within column N or returns blank if there is no third
occurrence; and your second formula takes a value from column S at the
same
row as the first occurrence of AL527 within column N or returns blank if
it
doesn't occur.

So, you want a single formula that takes a value from column E at the
same
row as the third occurrence of AL527 within column N, but only if...
<and
then the summary kind of falls apart, because the first could only work
if
the second were true anyway

Can you phrase your overall requirements in a similar sentence?




"winnie123" wrote in message
...
Hello All,

This is on Excel 2007
I would like to see if the array formula below can be changed to check
for
another criteria.
Want this currently does is finds all occurances of cell AL527(despatch
note) and matches that with the sheet "Data" col N (despatch note) and
the
result is taken from sheet "Data" col E (qty). If no match found then
the
result is ""
The reason the ROW 3 at the end has $ is it helps with copying the
formula
as I am using it for Columns rather than rows, I could not find a
formula
that worked for colums.

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$ 1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3)) ),"")}


I now want to find all occurances of cell AL527 as before but only if
the
value in cell BR527 is matched with a value from sheet "Data" col S

This array formula is in cell BR527

AL527(despatch note), Col N (despatch note), Col S (period)

{=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDE X(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0) ))}


Is it possible to combine the 2 formula's together?
I have been trying but cant even get a formula to be accepted :-(

Thanks
Winnie