Index and Match

[winnie123]

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)),"",INDEX(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

 

[Steve Dunn]

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]

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]

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),INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),3)))),"")