Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions | |||
VLOOKUP with duplicate returns | Excel Discussion (Misc queries) | |||
Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |