Return L, M OR H

[motilulla]

Hello,

Step1 - I am looking some Formula or VBA which can look B9 value (it can be alphabets "A To Z" or could be Number "1 to 9") in this example B9 value is "2", I want look this value "2" into C7 (most right alphabet or number 2 not found) look into D7 (most right alphabet or number 2 not found) look into E7 yes most right number is 2

Step2- Look value before slash "/" into C3 = 22, into D3 = 215, into E3 =712 can we say 22 is the lowest = L , 215 is a Medium = M , and 712 is a highest = H (so far there are 3 values L, M, H)

Step3- I want result H in cell G9 as 2 is find in E7 so in the same column E3 is the highest value = H result in G9

Example

	A​	B​	C​	D​	E​	F​	G​
1​
2​
3​			22 / 183​	215 / 324​	712 / 286​
4​
5​
6​
7​			W|T|A​	W|T|B​	W|T|2​
8​
9​		2​					H​

<tbody>
</tbody>

It is complicate hope I could have explained it.....

Thank you all
Excel 2000
Regards,
Moti

 

[Scott Huish]

Does this work for you?

=IFERROR(INDEX({"L","M","H"},MATCH("*"&B9&"*",C7:E7,0)),"")

 

[mopp1]

CSE formula
=CHOOSE(MATCH(MATCH($B$9&"",RIGHT($C$7:$E$7),0),MATCH(SMALL(LEFT($C$3:$E$3,FIND("/",$C$3:$E$3)-1)*1,{1,2,3}),LEFT($C$3:$E$3,FIND("/",$C$3:$E$3)-1)*1,0),0),"L","M","H")

 

[motilulla]

Does this work for you?

=IFERROR(INDEX({"L","M","H"},MATCH("*"&B9&"*",C7:E7,0)),"")

Scott Huishi, my excel version does not have function (IFERROR) and I wanted to look the value into the row 7 and it should give me the result L, H, and M from row 3.

Thank you for the reply

Have a good day.

Kind regards,
Moti

 

[motilulla]

CSE formula
=CHOOSE(MATCH(MATCH($B$9&"",RIGHT($C$7:$E$7),0),MATCH(SMALL(LEFT($C$3:$E$3,FIND("/",$C$3:$E$3)-1)*1,{1,2,3}),LEFT($C$3:$E$3,FIND("/",$C$3:$E$3)-1)*1,0),0),"L","M","H")

mopp1, you have caught the idea. Your formula is giving a perfect result as requested.

Thank you for your help.

Have a nice day.

Kind Regards,
Moti