Dynamic Formula Modification Help

[Newbienew]

I have a formula that I need to update to account for a modified entry. B4 may change depending on no requirements, C4 is dynamic based on what is in B4. If the entry in B4 starts with a 2 I need the C4 to multiply by 2 and if not just return what is supposed to return.

I attempted to use IF function but I keep having to change the Match function to -1 for greater than and the multiplication is 1.43, which I don't understand why.

2MK	<100
VE	<50
VE	<50

 

[etaf]

c4 multiply what by 2 ???
=IF( Left(b4,1)*1=2, Cell * 2 , cell)
I times by 1 as often with left the result is text
or
=IF(LEFT(B4,1)="2",2,0)

Book3
	A	B	C	D	E
1
2
3
4		2MK	<100		times x 2
5		VE	<50		cell
6		VE	<50		cell
Sheet1

Cell Formulas
Range	Formula
E4:E6	E4	=IF(LEFT(B4,1)="2","times x 2","cell")

 

[Newbienew]

I want to apologize. I thought I posted the mini sheet that I had. Found out the mini sheet option would freeze my excel program.

This is what I have.

ASVAB CAL.xlsb
	B	C
4	VE	<50
5	VE	<50
6	VE	<50
RATINGS (2)

Cell Formulas
Range	Formula
C4:C6	C4	=IFERROR(INDEX(HOME!$A$3:$L$3,MATCH('RATINGS (2)'!B4,HOME!$A$2:$L$2,0)),0)

So if there is 2VE or 2 in front of any combination of letters I would like for it to mulitipy by 2 and if not just return the original value.

 

[Peter_SSs]

Can you show us a mini sheet of 'HOME' range A2:L3 that produced the mini sheet shown above?

 

[Newbienew]

Yes I can

ASVAB CAL.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
2	GS	AR	WK	PC	MK	EI	AS	MC	AO	VE	CS	CT
3	50	50	50	50	50	50	50	50	50	50	50	70
HOME

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2
3
4		2VE	100
5		VE	50
6		2CT	140
Master

Cell Formulas
Range	Formula
C4:C6	C4	=IFNA(INDEX(Home!$A$3:$L$3,MATCH(RIGHT(B4,2),Home!$A$2:$L$2,0)),0)*IF(ISNUMBER(--LEFT(B4)),LEFT(B4),1)

 

[Peter_SSs]

Yes I can

Thanks

A bit unsure about what else might be possible in column B, but this may be another possibility.

Note that both @Fluff and I have changed 'RATINGS (2)'!B4 to just B4. Using the sheet name that the formula is on in a formula is unnecessary and can lead to incorrect results under some circumstances so should be avoided.

Newbienew.xlsm
	A	B	C
1
2
3
4		2VE	100
5		VE	50
6		2CT	140
7		XX	0
RATINGS (2)

Cell Formulas
Range	Formula
C4:C7	C4	=IF(LEN(B4)=2,1,2)*IFNA(HLOOKUP(RIGHT(B4,2),HOME!$A$2:$L$3,2,0),0)

 

[Newbienew]

I tried to mark both of them as the solutions but was not able to but

@Fluff Thank you so much for your aid.
@Peter_SSs Thank you as well. Yes I did notice you both change the formula. I changed to match what you have but when I was making the formula I bounced between pages and that happened. But I do get how it can mess up a formula

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.