ISERROR and INDEX Funcion question

[Flipper]

I have the following formula in column 4, row 4 of a multi row/column
spreadsheet, where I am referencing a value from one worksheet and looking
for the same value in an adjacent worksheet. It's basically a VLOOKUP
function.

=IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily
Activity'!$A$1:$A$77=$B4,ROW('Daily
Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Daily
Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily
Activity'!$A$1:$A$77)),ROW(1:$1)),2))

The function works as I want it to; however, if I copy the formula exactly
as is to say column 6, row 4, and then change the range from A1:a77 to
A81:A90, then the formula does not work. I can't figure out why. Can anyone
help?

Thanks.

 

[Bob Phillips]

See if this version is amendable

=IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily
Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily
Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)),"",
INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily
Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily
Activity'!$A$1:$A$77))+1),ROW(1:$1)),2))

 

[T. Valko]

What type of data is being returned? Is it text, numeric or could it be
either?

 

[Flipper]

Hi Bob
Thanks for the quick response. the new formula does not work either. I
obviously don't understand why changing the "lookup" range causes the formula
not to work. I want it to do the same thing, just based on a different range
of data.

 

[Flipper]

It should be numeric, but as it is, nothing is being returned.

 

[T. Valko]

Ok, here's one way that the formula can be written...

Let's assume that you want the formula entered in cell C4 then copied down.

Array entered:

=IF(ROWS(C$4:C4)>COUNTIF('Daily Activity'!
A$1:A$77,B$4),"",INDEX('Daily Activity'!B:B,
SMALL(IF('Daily Activity'!A$1:A$77=B$4,ROW
('Daily Activity'!A$1:A$77)),ROWS(C$4:C4))))

 

[Flipper]

I'm probably doing something wrong, because this doesn't work correctly
either. It does return a value, but it's the wrong one.

 

[T. Valko]

Here's a small sample file that demonstrates this.

zFlipper.xls 19kb

http://cjoint.com/?dAwA7hA0GL

 

[Flipper]

thanks for the assistance. I did get the formula below to work.