Solved Need an excel function to handle verticle lookup for dash #s

November 11, 2013 at 08:48:50
Specs: Windows 7
Basically I've got to do a vlookup to calculate two different price increases for a list of model numbers. But since some of the model numbers have dash's in them the vlookup doesn't include those numbers. It treats them as different numbers. I need it to recognize those numbers in the calc.


So I've got a column "A" which is a list of the basic model numbers(example 1a,3046,f50).

The second column "B" is a larger list of model numbers that include the same model numbers (1a,3046,f50), the same model numbers that include a dash (example 1a-10200, 3046-2a,f50-100), and numbers that aren't related (example 777, ffff1,z1000).

The third column "C" is a price list for the items of column "B".


I need to calculate a different percentage increase based on whether the basic model number is listed in column "B". So if the model number from column "A" (1a,3046,f50) shows up in column "B" I need to calculate the price of column "C" at a rate of 1.1, if the numbers don't match I need to calculate the price of column "C" at 1.05.


The problem is the dash screws up my calculation. I need to find a way to include those dashed numbers that have the same basic model number so I can calculate them at a 1.1 rate. Right now they're being calculated at a 1.05 rate in my formula.

Here's my formula right now

=IF(ISERROR(VLOOKUP(A1,$B$1:$B$4,1,FALSE)),C1*1.05,C1*1.1)


See More: Need an excel function to handle verticle lookup for dash #s

Report •


#1
November 11, 2013 at 09:23:55
I'm confused by your description of what Columns A and B contain.

The second column "B" is a larger list of model numbers that include the same model numbers (1a,3046,f50), the same model numbers that include a dash (example 1a-10200, 3046-2a,f50-100)

I read that to mean that all of the basic model numbers from A are also in B, plus there are the "dashed versions" of the basic numbers.

Is that what you have?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 11, 2013 at 09:32:58
In addtion, your example part number with the dashes also contains a space after the first comma. Is that a typo or do you have spaces as well as dashes in Column B?


The reason I ask is because if we need to parse the basic part number from the ones with the dashes, we need to know how what other characters they might contain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
November 11, 2013 at 10:07:41
✔ Best Answer
Assuming no space after 10200, the following formula will turn this:

1a-10200,3046-2a,f50-100 (a dashed model number)

into this:

1a,3046,f50 (a basic model number)

=LEFT(B1,FIND("-",B1)-1) & "," & MID(B1,FIND(",",B1)+1,FIND(CHAR(1),
SUBSTITUTE(B1,"-",CHAR(1),2))-FIND(",",B1)-1) & "," & MID(B1,FIND(CHAR(1),
SUBSTITUTE(B1,",",CHAR(1),2))+1,FIND(CHAR(1),
SUBSTITUTE(B1,"-",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),2))-1)

Assuming all "dashed model numbers" are laid out in the same manner, you should be able to use VLOOKUP on the parsed values since they will now match your "basic model numbers".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions


Ask Question