VLOOKUP

GDawg

New Member
Joined
Apr 24, 2002
Messages
20
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
=IF((A5>1)*(ISERROR(VLOOKUP(A5,Price!$A$2:$B$223,2,0))<>1),VLOOKUP(A5,Price!$A$2:$B$223,2,0),"")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top