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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>