Vlookup Multiple Returns #REF

[Guest]

Hi,

I am using the following formula and it has been working great but all of a
sudden today it stopped working and now shows #ref error or sometimes it will
give me a date of Jan 3 no matter what the date really should be. What is
wrong?

=INDEX(BDData!E:E,SMALL(IF(BDData!D$1$16200=E$5&B$11,ROW$1:$16200)),ROW(1:1)))

The lookup value is in BDData!D4010 and the value I want is in BDData!E4010

I use the similiar formula pulling from a different worksheet with great
success:
=INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$16,ROW($1:$16200)),ROW(1:1)))



Ben

 

[Biff]

Hi!

=INDEX(BDData!E:E,SMALL(IF(BDData!D$1$16200=E$5&B$11,ROW$1:$16200)),ROW(1:1)))

You're missing an opening ( at........ ROW$1:$16200)

Is that just a typo?

Biff

 

[Guest]

Good Catch. I fixed the typo but it still doesn't work. It used to work
great! Any other ideas?


=INDEX(BDData!E:E,SMALL(IF(BDData!D$1$19000=E$5&B$12,ROW($1:$19000)),ROW(2:2)))


Thanks,

Ben

Hi!

=INDEX(BDData!E:E,SMALL(IF(BDData!D$1$16200=E$5&B$11,ROW$1:$16200)),ROW(1:1)))

You're missing an opening ( at........ ROW$1:$16200)

Is that just a typo?

Biff

 

[Biff]

Hi!

Syntax-wise, there's nothing wrong with the formula. Of course I'm assuming
you know that it's an array formula?

What version of Excel are you using? If you're using Excel 2002 (XP) or
above try using the formula auditing tools to find where the REF is coming
from.

Not much else I can think of.

Biff

Good Catch. I fixed the typo but it still doesn't work. It used to work
great! Any other ideas?


=INDEX(BDData!E:E,SMALL(IF(BDData!D$1$19000=E$5&B$12,ROW($1:$19000)),ROW(2:2)))


Thanks,

Ben