Help with a VLOOKUP Formula

[tbruce]

So with help from the well of knowledge here, I was able to tackle my first issue of changing the first number in a value from one number to another, but now I'm looking to expand on the formula to handle a VLOOKUP to column A for a specific identifier "beta". If "beta" is present in the Type A column row & cell, then I want to apply the formula. If "beta" is not present (in this case "alpha" is), then simply copy the value from the Old Value B column cell to the New Value C column cell f the same row.

Any help you can provide to get me there would be much appreciated. Here is the current formula and an example data set:

=--REPLACE(J29,1,1,LEFT(J29,1)+OR(--LEFT(J29,1)={1,2,3}))

This works to change the first digit from the Old Value column cell from 1 to 2, 2 to 3 and 3 to 4 when I manually filter the Type A column to "beta" then apply the filter and copy the formula down.

I'm hoping someone can assist with a VLOOKUP to the current formula. I do need to copy ZERO as well if present. The current formula handles that, but need it to also copy ZERO when beta is not present and the value is ZERO in the Old Value column cell.

Hope this all makes sense.

	A	B	C
1	Type	Old Value	New Value
2	alpha	0
3	alpha	20695
4	alpha	20695
5	alpha	20695
6	alpha	20695
7	beta	10660	20660
8	beta	0	0
9	beta	0	0
10	beta	0	0
11	beta	30139	40139
12	alpha	40139
13	alpha	0
14	alpha	0
15	beta	30139	40139
16	beta	0	0
17	beta	0	0
18	beta	0	0

 

[rondeondo]

Hi there
Not sure what you're needing to lookup with vlookup, maybe I need more information

=if(a2="Beta",if(isnumber(left(b2,1)),if(left(b2,1)<9,left(b2,1)+1 ,"0"),left(b2,1)) & mid(b2,2,len(b2)-1),b2)

 

[Peter_SSs]

Try

20 09 10.xlsm
	A	B	C
1	Type	Old Value	New Value
2	alpha	0	0
3	alpha	20695	20695
4	alpha	20695	20695
5	alpha	20695	20695
6	alpha	20695	20695
7	beta	10660	20660
8	beta	0	0
9	beta	0	0
10	beta	0	0
11	beta	30139	40139
12	alpha	40139	40139
13	alpha	0	0
14	alpha	0	0
15	beta	30139	40139
16	beta	0	0
17	beta	0	0
18	beta	0	0
IF_

Cell Formulas
Range	Formula
C2:C18	C2	=IF(A2="beta",--REPLACE(B2,1,1,LEFT(B2,1)+OR(--LEFT(B2,1)={1,2,3})),B2)

 

[tbruce]

Thank you Peter - you're solution worked nicely!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.