IF Statement Modifications
 

Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

IF Statement Modifications
 

Maybe a different approach now you have more variables

=LOOKUP(B7,{"H","K","M","P"},{"JER","SHJ","MCJ","M OP"})

Mike

"Workbook" wrote:

Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

IF Statement Modifications
 

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

IF Statement Modifications
 

Very nice

"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

IF Statement Modifications
 

Hi,

You may also use the VLOOKUP() function. Please try
=VLOOKUP(B7,$C$9:$D$12,2,0). C$9:$D$12 is a table with two columns where
column C has H,K,M and P. Col. D has the acronyms

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Workbook" wrote in message
...
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that
if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

IF Statement Modifications
 

This is great. It works awesome. Thanks Mike.

I am wondering, Is it possible to adjust this part of the statement
,{"H","K","M","P"} further to accomodate 10 different words of varying
lengths?

For instance, when I've tried something like this ,{"Wireless
Laptop","Desktop Computer","Flatscreen Monitor","Server","Solar
Panels","I-Phone","I-Pod","Dual Screen Monitor Stand","Wirless
Router"}{"JERA","SHJK","MCJM","MOPQ","ABPE","POMZ" ,"PDAI","COPN","ZTYL","HLMW"})
, some of them work and some of them don't and I couldn't figure out why.
Any thoughts?

"Mike H" wrote:

Maybe a different approach now you have more variables

=LOOKUP(B7,{"H","K","M","P"},{"JER","SHJ","MCJ","M OP"})

Mike

"Workbook" wrote:

Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

IF Statement Modifications
 

Thanks Glen, this is really cool. It works great.

Is this part of the code ")-1)*3+1,3)) saying something to the effect of
(excuse my VBA translation), count 3 letters there and stick 3 letters here,
count 1 letter here and stick one letter there? Does my question make sense?




"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

IF Statement Modifications
 

Agreed!

"Mike H" wrote:

Very nice

"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

IF Statement Modifications
 

Thank you for you're input. I am not sure if I implemented this formula
correctly. I had some trouble making it work. I wonder if it's something I
did wrong.

"Ashish Mathur" wrote:

Hi,

You may also use the VLOOKUP() function. Please try
=VLOOKUP(B7,$C$9:$D$12,2,0). C$9:$D$12 is a table with two columns where
column C has H,K,M and P. Col. D has the acronyms

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Workbook" wrote in message
...
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that
if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

IF Statement Modifications
 

The statement (FIND(B7,"HKMP")-1) will return a number from 0 to 3 depending
upon the contents of B7. That result times 3 plus 1 (*3+1) turns {0, 1, 2, 3}
into {1, 4, 7, 10}. So, assuming B7 contains "M", you would get 7. The rest is
straight forward:

MID("JERSHJMCJNOP",7,3)="MCJ"


Workbook wrote:
Thanks Glen, this is really cool. It works great.

Is this part of the code ")-1)*3+1,3)) saying something to the effect of
(excuse my VBA translation), count 3 letters there and stick 3 letters here,
count 1 letter here and stick one letter there? Does my question make sense?




"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7


One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))