Lookup __ problem

[yosegmail]

<tbody>
[TD="align: center"][/TD]
[TD="class: xl66, width: 55, align: center"]A[/TD]
[TD="class: xl66, width: 79, align: center"]B[/TD]
[TD="class: xl66, width: 49, align: center"]C[/TD]
[TD="class: xl66, width: 46, align: center"]D[/TD]
[TD="class: xl66, width: 49, align: center"]E[/TD]

[TD="align: center"]1[/TD]
[TD="class: xl65, align: center"]0.000[/TD]
[TD="align: center"]FLAT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="class: xl65, align: center"]20.000[/TD]
[TD="align: center"]ROLLING[/TD]
[TD="class: xl65, align: center"]0.000[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="class: xl65, align: center"]40.000[/TD]
[TD="align: center"]ROLLING[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="class: xl65, align: center"]60.000[/TD]
[TD="align: center"]ROLLING[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="class: xl65, align: center"]80.000[/TD]
[TD="align: center"]FLAT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="class: xl65, align: center"]100.000[/TD]
[TD="align: center"]FLAT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="class: xl65, align: center"]120.000[/TD]
[TD="align: center"]FLAT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="class: xl65, align: center"]140.000[/TD]
[TD="align: center"]ROLLING[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="class: xl65, align: center"]160.000[/TD]
[TD="align: center"]ROLLING[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="class: xl65, align: center"]180.000[/TD]
[TD="align: center"]ROLLING[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

I want to Lookup in Column B and return in Cell E2 like the following
0-60 _____ ROLLING
60-120 ____ FLAT
120-180 ___ ROLLING
The Ranges are in Column A and they are inclusive
I tried MODE function to tell me frequent and came up with like this
=IF(if(C1<a1:a10,d1>A1:A10),B1:B10)
{"ROLLING";"ROLLING";"ROLLING";"FLAT";FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

THANK YOU</a1:a10,d1>

 

[yosegmail]

Sorry I said MODE function, I mean IF function...
it is an array ...
ANY formulas???

 

[yosegmail]

this formula would do the trick..
={IFERROR(INDEX(INDIRECT("b"&MATCH(C2,A:A,1)):INDIRECT("b"&MATCH(D2,A:A,1)),MATCH(MODE(LEN($B$1:INDIRECT("b"&MATCH(D2,A:A,1)))),LEN($B$1:INDIRECT("b"&MATCH(D2,A:A,1))),0)),"")}

many solutions... i came up with this.

 

[Aladin Akyurek]

What are the look up table and look up value here?

 

[yosegmail]

the lookup value is the character from column B... "ROLLING","FLAT".....
the one that appears most between the range specified in cell c2 and d2 in column B is needed in cell E2
In cell E2 is where the formula goes..
That is the case

 

[Aladin Akyurek]

the lookup value is the character from column B... "ROLLING","FLAT".....
the one that appears most between the range specified in cell c2 and d2 in column B is needed in cell E2
In cell E2 is where the formula goes..
That is the case

Thanks...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]

0​

[/td][td]

FLAT​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

20​

[/td][td]

ROLLING​

[/td][td]

0​

[/td][td]

60​

[/td][td]

ROLLING​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

3​

[/td][td]

40​

[/td][td]

ROLLING​

[/td][td]

60​

[/td][td]

120​

[/td][td]

FLAT​

[/td][td]

SQUARE​

[/td][td]

​

[/td][/tr]
[tr][td]

4​

[/td][td]

50​

[/td][td]

ROLLING​

[/td][td]

120​

[/td][td]

180​

[/td][td]

ROLLING​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

5​

[/td][td]

80​

[/td][td]

FLAT​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

6​

[/td][td]

100​

[/td][td]

FLAT​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

7​

[/td][td]

110​

[/td][td]

SQUARE​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

8​

[/td][td]

115​

[/td][td]

SQUARE​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

9​

[/td][td]

120​

[/td][td]

FLAT​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

140​

[/td][td]

ROLLING​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

160​

[/td][td]

ROLLING​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

180​

[/td][td]

ROLLING​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

A1:A12 is defined as Score.

B1:B12 is defined as Qual (from qualification).

Ivec is defined in the Name Manager as referring to:

=ROW(Qual)-ROW(INDEX(Qual,1,1))+1

QualFreqSet is defined in the Name Manager as referring to:

=FREQUENCY(IF(1-(Qual=""),IF(Score>=Sheet1!$C1,IF(Score<Sheet1!$D1,MATCH(Qual,Qual,0)))),Ivec)

In E2 control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($E2:E2)>SUM(IF(LARGE(QualFreqSet,Ivec)>=LARGE(QualFreqSet,1),1)),"",INDEX(Qual,SMALL(IF(QualFreqSet=LARGE(QualFreqSet,COLUMNS($E2:E2)),Ivec),SUM(IF(LARGE(QualFreqSet,COLUMN($E2:E2)-COLUMN($E2)+1)=LARGE(QualFreqSet,COLUMNS($E2:E2)),1)))))

Hope this helps.

 

[markmzz]

this formula would do the trick..
={IFERROR(INDEX(INDIRECT("b"&MATCH(C2,A:A,1)):INDIRECT("b"&MATCH(D2,A:A,1)),
MATCH(MODE(LEN($B$1:INDIRECT("b"&MATCH(D2,A:A,1)))),LEN($B$1:INDIRECT("b"&MATCH(D2,A:A,1))),0)),"")}

many solutions... i came up with this.

Hi Yosegmail,

Maybe this small modification (in red) in the second part of your formula can helps.

Normal Formula - use only Enter to enter the formula

=IFERROR(INDEX(INDIRECT("B"&MATCH(C2,A:A,1)):INDIRECT("B"&MATCH(D2,A:A,1)),
MODE(MATCH(INDIRECT("B"&MATCH(C2,A:A,1)):INDIRECT("B"&MATCH(D2,A:A,1)),
INDIRECT("B"&MATCH(C2,A:A,1)):INDIRECT("B"&MATCH(D2,A:A,1)),0))),"")

Markmzz

 

[yosegmail]

Thanks all.
I would appreciate if you have some VBA code for this.

 

[markmzz]

Thanks all.
I would appreciate if you have some VBA code for this.

Hi!

Here is another formula (without INDIRECT function):

=IFERROR(INDEX(INDEX(B:B,MATCH(C2,A:A)):INDEX(B:B,MATCH(D2,A:A)),
MODE(MATCH(INDEX(B:B,MATCH(C2,A:A)):INDEX(B:B,MATCH(D2,A:A)),
INDEX(B:B,MATCH(C2,A:A)):INDEX(B:B,MATCH(D2,A:A)),0))),"")

Do some tests.

About VBA code:

I'm sorry, but in 2018 posts I have avoided suggestions using VBA.

Maybe another user can help you with a VBA code. Lets wait.

Markmzz

 

[Aladin Akyurek]

...
I would appreciate if you have some VBA code for this.

Thanks for the update. I hope someone into VBA will take this up.