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

[zacemmel]

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

[Asala42]

G'day,

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

How about:

=IF(OR(A5<=1,COUNTIF($A$2:$A$223,A5)),"",VLOOKUP(A5,Price!$A$2:$B$223,2,0))

Adam

[Dave Patton]

I do not have your data; consequently, I did not test the following.

=IF(AND(LEN(A5),COUNTIF(Price!$A$2:$B$223,A5)),VLOOKUP(A5,Price!$A$2:$B$223,2,FALSE),"")

If A5 and A5 not found show blank
find A5 in range
show data from column 2
exact match


Please advise if this works or provide
feedback.

[GDawg]

Thanks Dave but... interestingly it returns data for the first series of B's but won't return any values for numbers in the BB and beyond. On my original formula I always got a return but after the single B's it would be one value behind.

Here's a slice of the unit numbers and correspondening prices:

B72 230.00
B78 250.00
B84 264.00
B90 266.00
B96 288.00
BB36 120.00
BB39 122.00
BB42 124.00
BB45 134.00
BB48 142.00
DB21 142.00
DB24 152.00
DB27 158.00
DB30 164.00
RB30 114.00
RB33 120.00
RB36 124.00
RB39 132.00
RB42 138.00

[Aladin Akyurek]

On 2002-05-07 11:16, GDawg wrote:
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

=IF(AND(LEN(A5),COUNTIF(Price!$A$2:$A$223,A5)),VLOOKUP(A5,Price!$A$2:$B$223,2,0),"")

I believe even

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

would suffice.

Aladin

[Dave Patton]

The formula works OK for me.

Check for extra spaces.

You can compare the len of say BB36 at
both locations or

copy the lookup value BB36 to the LookupTable and see if you get proper result.

[GDawg]

Aladin - same as with Dave.

Either formula will work in the first series of B values, but once you get to the BB's and up nothing is retuned.

Thanks,

GDawg

[Aladin Akyurek]

On 2002-05-07 11:56, GDawg wrote:
Aladin - same as with Dave.

Either formula will work in the first series of B values, but once you get to the BB's and up nothing is retuned.

Thanks,

GDawg

As Dave noted, check for extraneous spaces both around the lookup values as well as the values in the first column of the lookup table. You can check this with LEN (Note however that the LEN test is not needed in the VLOOKUP formula itself).

Aladin

[GDawg]

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