IF Statement Modifications

[Workbook]

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

 

[Mike H]

Maybe a different approach now you have more variables

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

Mike

 

[Glenn]

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))

 

[Mike H]

Very nice

One way:

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

 

[Ashish Mathur]

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]

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?

 

[Workbook]

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?

 

[Workbook]

Agreed!

Very nice

 

[Workbook]

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.

 

[Glenn]

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"