Thanks:  0
Likes:  0

1. On 2002-05-07 12:42, GDawg wrote:

By extraneous spaces do you mean blank cells or cells that have value not relative to the table? There are none.

Many thanks,

GDawg
Take BB36 as example housed in Q1. If

=LEN(Q1)=4 does not hold, it's pretty sure that

=LEN(TRIM(Q1)) will result in 4.

if the above scenario obtains, we'd say the entry in Q1 had unintended space(s) (whose ASCII code 32) in front and/or at the back.

2. That worked!!! There were extra spaces in a majority of the cells. Many, many thanks. One more question though - if the exact number say BB37 doesn't exist is there a way to have it return the next higher value, i.e., the value for a BB39?

Thanks,

GDawg

3. On 2002-05-07 13:22, GDawg wrote:
That worked!!! There were extra spaces in a majority of the cells. Many, many thanks. One more question though - if the exact number say BB37 doesn't exist is there a way to have it return the next higher value, i.e., the value for a BB39?

Thanks,

GDawg
Array-enter:

=IF(COUNTIF(Price!\$A\$2:\$A\$223,A5),VLOOKUP(A5,Price!\$A\$2:\$B\$223,2,0),INDEX(Price!\$B\$2:\$B\$223,MIN(IF((LEN(Price!\$A\$2:\$A\$22 3)=LEN(A5))*(RIGHT(Price!\$A\$2:\$A\$223,2)>=RIGHT(A5,2)),(ROW(Price!\$A\$2:\$A\$223))))))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Not sure whether this is what you're looking for.

Keep also in mind that this is an expensive formula.

[ This Message was edited by: Aladin Akyurek on 2002-05-08 05:51 ]

You're right - that formula is too expensive. Would it be simpler to return a message that read INVALID NO. or something like that?

Thanks,

GDawg

5. On 2002-05-08 05:11, GDawg wrote:

You're right - that formula is too expensive. Would it be simpler to return a message that read INVALID NO. or something like that?

Thanks,

GDawg
=IF(COUNTIF(Price!\$A\$2:\$A\$223,A5),VLOOKUP(A5,Price!\$A\$2:\$B\$223,2,0),"Not Available")

should be OK no?

BTW, does the array-formula return the results as desired? If so, we can tradeoff space (memory) against time (speed) and see whether that's acceptable.

The "Not Available" formula worked fine. As for as the monster one I couldn't get it to work. First Excel claimed it had "(" problems and if I let Excel change the formula it would return some value but never the right one.

I need to learn more of the rudiments of Excel formulas - do your recommend a good book!!

Thanks,

GDawg

7. HELLO THERE

I think it's time for Aladin to write a book about excel formulas.It will be a top one
Till then try John's Walkenbach book "excel 2000 formulas"

8. On 2002-05-08 05:43, GDawg wrote:

The "Not Available" formula worked fine. As for as the monster one I couldn't get it to work. First Excel claimed it had "(" problems and if I let Excel change the formula it would return some value but never the right one.

I need to learn more of the rudiments of Excel formulas - do your recommend a good book!!

Thanks,

GDawg
The monster needs a closing paren, an omission due to glueing to formulas into by Copy and Paste. It works but the question is Does it work the intended way? You can get a copy of that if you want to.

No, the monster never worked the intended way at least for me.

I appreciate the help from you and the others that contributed.

Keep up the great work.

Ciao,

GDawg

10. On 2002-05-08 06:36, GDawg wrote:

No, the monster never worked the intended way at least for me.

I appreciate the help from you and the others that contributed.

Keep up the great work.

Ciao,

GDawg
Ciao... but not yet...

If the lookup value is BB36, given the sample I get 120 as result.

If the lookup value is BB43 (which does not exist lterally in the sample), I get 142 as result.

Interested?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•