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
 
Which formula? The monster? I gave up on it and returned to the one that gave blanks if there was no match. Did the value you got come from the next highest one available?

GDawg
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
On 2002-05-08 07:52, GDawg wrote:
Which formula? The monster? I gave up on it and returned to the one that gave blanks if there was no match. Did the value you got come from the next highest one available?

GDawg

This is the sample that you provided yourself:

{"Item","Price";
"B72",230;
"B78",250;
"B84",264;
"B90",266;
"B96",288;
"BB36",120;
"BB39",122;
"BB42",124;
"BB45",134;
"BB48",142;
"DB21",142;
"DB24",152;
"DB27",158;
"DB30",164;
"RB30",114;
"RB33",120;
"RB36",124;
"RB39",132;
"RB42",138}

Bolded row where it looks for BB43.

Aladin
 
Upvote 0
I just wanted to let you guys know that I was having a similar problem yesterday and reading this thread help immensely!

Y'all are so smart!
 
Upvote 0
Aladin,

Ture - if I enter BB43 it doesn't hit the next higher value but the one above that. I'm interested but don't know why it's doing that or how to change it. Otherwise the monster formular works fine.

GDawg
 
Upvote 0
Ture - if I enter BB43 it doesn't hit the next higher value but the one above that. I'm interested but don't know why it's doing that or how to change it. Otherwise the monster formular works fine.

It's about time you get a copy the WB that uses the array formula we are discussing. Just drop me a line at:

aladin_akyurek@yahoo.com
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
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