Need solution for my problem ( Excel Formula )

A

amarbhagwat

New Member
Joined
Jun 14, 2013
Messages
2
Hi Experts

This is my first post so apology if i have done any mistake while posting the thread.

I am looking for an excel formula to get the result. Data is provided in below table

There are 3 criteria based on which a single value needs to pick automatically.

criteria 1 = value 1 variable e.g 92.5%
criteria 2 = value 2 variable e.g. 32%
criteria 3 = value 3 variable e.g. 27.5 year

I need a formula which will give me answer by considering all three criteria

[TABLE="width: 500"]
<tbody>[TR]
[TD]value 1[/TD]
[TD]value 2[/TD]
[TD]value 3[/TD]
[TD]answer ( formula)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]criteria C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]criteria a[/TD]
[TD]criteria B[/TD]
[TD]25.1 to 30 Years[/TD]
[TD]25 Years or less[/TD]
[/TR]
[TR]
[TD]95.01% and above[/TD]
[TD]35[/TD]
[TD]0.96[/TD]
[TD]0.85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30[/TD]
[TD]0.84[/TD]
[TD]0.73[/TD]
[/TR]
[TR]
[TD]95% to 90.01 %[/TD]
[TD]30[/TD]
[TD]0.78[/TD]
[TD]0.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]25[/TD]
[TD]0.67[/TD]
[TD]0.56[/TD]
[/TR]
[TR]
[TD]90% to 85.01[/TD]
[TD]30[/TD]
[TD]0.60[/TD]
[TD]0.49[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]25[/TD]
[TD]0.52[/TD]
[TD]0.41[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD]0.42[/TD]
[TD]0.31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12[/TD]
[TD]0.34[/TD]
[TD]0.23[/TD]
[/TR]
[TR]
[TD]85% and under[/TD]
[TD]25[/TD]
[TD]0.43[/TD]
[TD]0.32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD]0.39[/TD]
[TD]0.28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12[/TD]
[TD]0.32[/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]06[/TD]
[TD]0.27[/TD]
[TD]0.19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Your expert help is really helpful. I have tried the fusion of lookup with index function but my efforts were went into vain.

Regards

Amar
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Your post is perfect AmarBhagwat but with one big mistake: It's not clear...
Thank you for the table example but could you explain more?!
With pleasure
ZAX
 
Upvote 0
ZAX said:
Your post is perfect AmarBhagwat but with one big mistake: It's not clear...
Thank you for the table example but could you explain more?!
With pleasure
ZAX
Click to expand...



Hello Sir

Here is more explanation


criteria A contains 4 conditions as below

95.01% and above
95% to 90.01%
90 to 85.01%
85% and below

criteria B values are based on criteria A
e.g. for criteria A if condition ( 95.01% and above ) meets then criteria B will be searched in 2 conditions 35 and 30
when any one condition is matched in criteria B, it should look for criteria C

criteria C contains only 2 conditions ( 25.1 to 30 Years and 25 Years or less )

Final answer should come from criteria C ( entries given in 25.1 to 30 Years column or 25 Years or Less column).

But to get the Answer Value from Criteria C ( 2 columns ) , the data should meet the criteria A and B with respective conditions

It is like reading Algorithm book with 3 criteria


Regards

Amar
 
Upvote 0
Hello Amar

I changed the layout for the criteria into ascending order.

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
[TD="colspan: 5, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Crit.C[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Crit.A[/TD]
[TD="align: left"]Crit.B[/TD]
[TD="bgcolor: #DDD9C3, align: right"]0[/TD]
[TD="bgcolor: #DDD9C3, align: right"]25,1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]6%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,19[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,27[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]12%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,2[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,32[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]20%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,28[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,39[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,2[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,43[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]12%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,23[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,34[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]20%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,31[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,42[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,41[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,52[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,49[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,6[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #FDE9D9, align: right"]90,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,56[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #FDE9D9, align: right"]90,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,78[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #FDE9D9, align: right"]95,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,73[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,84[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #FDE9D9, align: right"]95,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]35%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,85[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,96[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]Crit.A[/TD]
[TD="align: left"]Crit.B[/TD]
[TD="align: left"]Crit.C[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="bgcolor: #FDE9D9, align: right"]92,50%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]32%[/TD]
[TD="bgcolor: #DDD9C3, align: right"]27,5[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,78[/TD]

</tbody>

ZelleFormel
D19=INDEX($C$3:$D$14;VERWEIS(42;1/(($A$3:$A$14<=A19)*($B$3:$B$14<=B19));ZEILE($1:$10));VERGLEICH(C19;$C$2:$D$2;1))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
 
Upvote 0
shift-del said:
Hello Amar

I changed the layout for the criteria into ascending order.

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
[TD="colspan: 5, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Crit.C[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Crit.A[/TD]
[TD="align: left"]Crit.B[/TD]
[TD="bgcolor: #DDD9C3, align: right"]0[/TD]
[TD="bgcolor: #DDD9C3, align: right"]25,1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]6%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,19[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,27[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]12%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,2[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,32[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]20%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,28[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,39[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,2[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,43[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]12%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,23[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,34[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]20%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,31[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,42[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,41[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,52[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,49[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,6[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #FDE9D9, align: right"]90,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,56[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #FDE9D9, align: right"]90,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,78[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #FDE9D9, align: right"]95,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,73[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,84[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #FDE9D9, align: right"]95,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]35%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,85[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,96[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]Crit.A[/TD]
[TD="align: left"]Crit.B[/TD]
[TD="align: left"]Crit.C[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="bgcolor: #FDE9D9, align: right"]92,50%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]32%[/TD]
[TD="bgcolor: #DDD9C3, align: right"]27,5[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,78[/TD]

</tbody>

ZelleFormel
D19=INDEX($C$3:$D$14;VERWEIS(42;1/(($A$3:$A$14<=A19)*($B$3:$B$14<=B19));ZEILE($1:$10));VERGLEICH(C19;$C$2:$D$2;1))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
Click to expand...

WoW! Your name is a clue to something I never ever heard about,It's a mystery!
Or do you even know that??
ZAX
 
Upvote 0
shift-del said:
Hello Amar

I changed the layout for the criteria into ascending order.

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
[TD="colspan: 5, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]Crit.C[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Crit.A[/TD]
[TD="align: left"]Crit.B[/TD]
[TD="bgcolor: #DDD9C3, align: right"]0[/TD]
[TD="bgcolor: #DDD9C3, align: right"]25,1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]6%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,19[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,27[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]12%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,2[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,32[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]20%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,28[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,39[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #FDE9D9, align: right"]0[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,2[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,43[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]12%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,23[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,34[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]20%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,31[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,42[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,41[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,52[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #FDE9D9, align: right"]85,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,49[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,6[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #FDE9D9, align: right"]90,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]25%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,56[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #FDE9D9, align: right"]90,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,78[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #FDE9D9, align: right"]95,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]30%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,73[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,84[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #FDE9D9, align: right"]95,01%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]35%[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,85[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,96[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]Crit.A[/TD]
[TD="align: left"]Crit.B[/TD]
[TD="align: left"]Crit.C[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="bgcolor: #FDE9D9, align: right"]92,50%[/TD]
[TD="bgcolor: #DBEEF3, align: right"]32%[/TD]
[TD="bgcolor: #DDD9C3, align: right"]27,5[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,78[/TD]

</tbody>

ZelleFormel
D19=INDEX($C$3:$D$14;VERWEIS(42;1/(($A$3:$A$14<=A19)*($B$3:$B$14<=B19));ZEILE($1:$10));VERGLEICH(C19;$C$2:$D$2;1))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
Click to expand...

WoW! Your name is a clue to something I never ever heard about,It's a mystery!
Or do you even know that??
ZAX
 
Upvote 0
Sorry, I just noticed I didn't switch the language.


<colgroup><col style="width: 28ptpx"><col width="60pt"></colgroup><tbody>
[TD="colspan: 2, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]D[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="bgcolor: #EAF1DD, align: right"]0,67[/TD]

</tbody>

ZelleFormel
D19=INDEX($C$3:$D$14,LOOKUP(42,1/(($A$3:$A$14<=A19)*($B$3:$B$14<=B19)),ROW($1:$10)),MATCH(C19,$C$2:$D$2,1))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
 
Upvote 0
shift-del said:
Sorry, you lost me there.
Click to expand...
I think ZAX was surprised to find that selecting a cell, then pressing the SHIFT-DELETE key combination (from your posted name "shift-del(ete)"), then selecting a different cell and then pressing the Enter key moves the value in the cell in the same way that pressing CTRL+C, selecting a different cell and then pressing CTRL+V does. I think it was a new key combination for him.
 
Upvote 0
Thanks for clarifying, Rick.
Was the keystroke always working this way? I always thought it was just deleting.
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question Question
Conditional formatting cells up to first cell after group of consecutive zeros
Replies
1
Views
459
awoohaw
awoohaw
G
  • Question Question
Complex problem - how many widgets should I sell?
Replies
9
Views
636
Dan_W
D
T
  • Question Question
Add another criteria to Excel Nested FILTER (Spillable) Functions
Replies
2
Views
622
TheHack22
T
M
  • Question Question
Type Mismatch Error (Error 13) in a SELECT/CASE Routine (Nested For Each Loop) for Conditional Formatting
Replies
3
Views
851
DanteAmor
DanteAmor
W
  • Question Question
Need assistance fixing a VLOOKUP
Replies
7
Views
491
Fluff
Fluff

Forum statistics

Threads
1,222,827
Messages
6,168,482
Members
452,192
Latest member
FengXue

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