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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

T
  • Question Question
Find highest scorers and return their name
2
Replies
13
Views
147
Peter_SSs
Peter_SSs
P
  • Question Question
Count names based on enrollment
Replies
2
Views
222
paulkc
P
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
221
Alex Blakenburg
A
E
  • Question Question
Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.
Replies
4
Views
267
Event2020
E
R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
634
RapchikM
R

Forum statistics

Threads
1,223,705
Messages
6,173,989
Members
452,541
Latest member
haasro02

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