Vlookup Multiple Returns #REF

B

Ben

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:D$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
 
B

Biff

Hi!

=INDEX(BDData!E:E,SMALL(IF(BDData!D$1:D$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
 
B

Ben

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:D$19000=E$5&B$12,ROW($1:$19000)),ROW(2:2)))


Thanks,

Ben


Biff said:
Hi!

=INDEX(BDData!E:E,SMALL(IF(BDData!D$1:D$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
Click to expand...
 
B

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

Ben said:
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:D$19000=E$5&B$12,ROW($1:$19000)),ROW(2:2)))


Thanks,

Ben
Click to expand...
 
Top