Return L, M OR H

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
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

[TABLE="width: 480"]
<tbody>[TR]
[TD="class: xl64, width: 80, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
A​
[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
B​
[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
C​
[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
D​
[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
E​
[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
F​
[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]
G​
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
1​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
2​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
3​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
22 / 183​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
215 / 324​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
712 / 286​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
4​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
5​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
6​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
7​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
W|T|A​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
W|T|B​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
W|T|2​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
8​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]
9​
[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
2​
[/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white, align: center"][/TD]
[TD="class: xl63, width: 80, bgcolor: white"]
H​
[/TD]
[/TR]
</tbody>[/TABLE]


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

Thank you all
Excel 2000
Regards,
Moti
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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")
 
Upvote 0
Scott Huish said:
Does this work for you?

=IFERROR(INDEX({"L","M","H"},MATCH("*"&B9&"*",C7:E7,0)),"")
Click to expand...
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
 
Upvote 0
mopp1 said:
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")
Click to expand...
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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
T
  • Question Question
Find highest scorers and return their name
2
Replies
13
Views
252
Peter_SSs
Peter_SSs
P
  • Question Question
Count names based on enrollment
Replies
2
Views
235
paulkc
P
C
  • Question Question
Only Brings Unique Value Across
Replies
3
Views
107
kvsrinivasamurthy
K
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
289
Alex Blakenburg
A

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top