VLOOKUP

[GDawg]

I'm using VLOOKUP to return a unit price for a model code to sheet JOB01 from a price list on sheet Price. The formula

=IF(A5>1,VLOOKUP(A5,Price!$A$2:$B$223,2,Price!$B$2:$B$223),"")

doesn't fully work.

The models (in column A)change (sequentially increment) alphanumerically i.e., they begin B15, B18, etc. through B96 then shift to BB15, BB18, etc. and on and on through several alpha combinations. The formula will work through the B15 - B96 range but once you get into the BB's (or beyond) the price (from Col B) that is returned is always the price before the one you are seeking, i.e. if you enter BB24 you'll get the price for BB21. What's wrong with the formula? (Please write a working formular.)

Also if no value preceeds the formula, i.e. A5 is blank, the formula cell is #N/A - how can I get rid of it.

Thanks,

GDawg

 

[Aladin Akyurek]

On 2002-05-07 12:42, GDawg wrote:
Aladin

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.

Aladin

 

[GDawg]

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

 

[Aladin Akyurek]

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$223)=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.

Aladin
This message was edited by Aladin Akyurek on 2002-05-08 05:51

 

[GDawg]

Aladin

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

 

[Aladin Akyurek]

On 2002-05-08 05:11, GDawg wrote:
Aladin

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.

Aladin

 

[GDawg]

Aladin,

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

 

[sen_edp]

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"

 

[Aladin Akyurek]

On 2002-05-08 05:43, GDawg wrote:
Aladin,

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.

Aladin

 

[GDawg]

Aladin,

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

 

[Aladin Akyurek]

On 2002-05-08 06:36, GDawg wrote:
Aladin,

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?

Aladin