Vlookup Multiple Returns #REF
 

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

--
Thanks,

Ben

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

"Ben" wrote in message
...
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

--
Thanks,

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" wrote:

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

"Ben" wrote in message
...
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

--
Thanks,

Ben

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" wrote in message
...
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" wrote:

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

"Ben" wrote in message
...
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

--
Thanks,

Ben