Simple IF

[hsandeep]

I know its simplest..still I need since I made mistake

Input Cell: B2 which can be EITHER of “d2” or “d3” or “d4” or “d5”. That’s All

Output Cell: D2:
IF B2=”d2” THEN D2=”k3”
IF B2=”d3” THEN D2=”k4”
IF B2=”d4” THEN D2=”k5”
IF B2=”d5” THEN D2=”k6”

How to accomplish?
I am using Excel 2007

 

[shg]

=INDEX({"k3";"k4";"k5";"k6"}, MATCH(B2, {"d2";"d3";"d4";"d5"}, 0))

 

[Fluff]

Another way
=IF(B2="","","k"&RIGHT(B2,1)+1)

 

[hsandeep]

=INDEX({"k3";"k4";"k5";"k6"}, MATCH(B2, {"d2";"d3";"d4";"d5"}, 0))

Nice. IT WORKS....Thanks shg

 

[hsandeep]

Another way
=IF(B2="","","k"&RIGHT(B2,1)+1)

Yes .........its another way .......but I may need "k300" also......so can't figure out how to use your formula. The FIRST alphabet is "k" followed by POSITIVE INTEGER.........which depends on another cell's formula.

 

[Fluff]

My suggestion was simply based on your original description.

 

[hsandeep]

My suggestion was simply based on your original description.

Yes ACCEPTED & your formula has solved my another problem..Thanks for that

 

[Fluff]

Glad we could help & thanks for the feedback

 

[hsandeep]

Glad we could help & thanks for the feedback

I want to ‘fill’ G2:G11 with FORMULAS ‘matching’ the text in B2

IF B2=”d2” THEN G2:G11 should be ‘filled’ with FORMULAS ‘=F3’, ‘=F4’ & so on till ‘=F12’

Similarly, IF B2=”d3” THEN G2:G11 should be ‘filled’ with FORMULAS ‘=F4’, ‘=F5’ & so on till ‘=F13’
Similarly, IF B2=”d4” THEN G2:G11 should be ‘filled’ with FORMULAS ‘=F5’, ‘=F6’ & so on till ‘=F14’

In other words, IF 2 is SUFFIXED in B2’s text (‘d2’), THEN G2 SHOULD START WITH 3 (2+1) AS SUFFIX of F (constant alphabet) & so the FORMULA in G2 would be ‘=F3” & G3 would be ‘=F4” & so on till G11

Formulas needed for G2:G11

How to accomplish?
Sheet1

	B	C	D	E	F	G
d2
d3
d4
d5

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 7px"><col style="WIDTH: 7px"><col style="WIDTH: 7px"><col style="WIDTH: 7px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]=F3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]=F4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]=F5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]=F12[/TD]

</tbody>

 

[Fluff]

How about
=INDIRECT("F"&RIGHT($B$2)+ROW(A1))