Vlookup or index (multiple selection) ??

B

bperc

New Member
Joined
Sep 19, 2013
Messages
1

<tbody>
[TD="class: xl65"][/TD]
[TD="class: xl71, colspan: 3"][/TD]
[TD="class: xl71, width: 111, colspan: 3"][/TD]
[TD="class: xl71, width: 111, colspan: 3"][/TD]
[TD="class: xl71, width: 111, colspan: 3"][/TD]
[TD="class: xl71, width: 111, colspan: 3"][/TD]

[TD="class: xl66, width: 72"]section[/TD]
[TD="class: xl74, width: 111, colspan: 3"]1[/TD]
[TD="class: xl74, width: 111, colspan: 3"]2[/TD]
[TD="class: xl74, width: 111, colspan: 3"]3[/TD]
[TD="class: xl74, width: 111, colspan: 3"]4[/TD]
[TD="class: xl74, width: 111, colspan: 3"]5[/TD]

[TD="class: xl67, width: 72"]selection[/TD]
[TD="class: xl68, width: 37"]R1[/TD]
[TD="class: xl68, width: 37"]R2[/TD]
[TD="class: xl68, width: 37"]R3[/TD]
[TD="class: xl68, width: 37"]R1[/TD]
[TD="class: xl68, width: 37"]R2[/TD]
[TD="class: xl68, width: 37"]R3[/TD]
[TD="class: xl68, width: 37"] R1[/TD]
[TD="class: xl68, width: 37"]R2[/TD]
[TD="class: xl68, width: 37"]R3[/TD]
[TD="class: xl68, width: 37"] R1[/TD]
[TD="class: xl68, width: 37"]R2[/TD]
[TD="class: xl68, width: 37"]R3[/TD]
[TD="class: xl68, width: 37"] R1[/TD]
[TD="class: xl68, width: 37"]R2[/TD]
[TD="class: xl68, width: 37"]R3[/TD]

[TD="class: xl69, width: 72"]1[/TD]
[TD="class: xl76, width: 37"]0,02[/TD]
[TD="class: xl77, width: 37"]0,03[/TD]
[TD="class: xl78, width: 37"]0,03[/TD]
[TD="class: xl85, width: 37"]0,05[/TD]
[TD="class: xl77, width: 37"]0,06[/TD]
[TD="class: xl78, width: 37"]0,07[/TD]
[TD="class: xl85, width: 37"]0,08[/TD]
[TD="class: xl77, width: 37"]0,1[/TD]
[TD="class: xl78, width: 37"]0,12[/TD]
[TD="class: xl85, width: 37"]0,12[/TD]
[TD="class: xl77, width: 37"]0,14[/TD]
[TD="class: xl78, width: 37"]0,17[/TD]
[TD="class: xl85, width: 37"]0,16[/TD]
[TD="class: xl77, width: 37"]0,19[/TD]
[TD="class: xl78, width: 37"]0,23[/TD]

[TD="class: xl69, width: 72"]2[/TD]
[TD="class: xl79, width: 37"]0,04[/TD]
[TD="class: xl80, width: 37"]0,05[/TD]
[TD="class: xl81, width: 37"]0,06[/TD]
[TD="class: xl86, width: 37"]0,09[/TD]
[TD="class: xl80, width: 37"]0,11[/TD]
[TD="class: xl81, width: 37"]0,14[/TD]
[TD="class: xl86, width: 37"]0,16[/TD]
[TD="class: xl80, width: 37"]0,19[/TD]
[TD="class: xl81, width: 37"]0,23[/TD]
[TD="class: xl86, width: 37"]0,23[/TD]
[TD="class: xl80, width: 37"]0,29[/TD]
[TD="class: xl81, width: 37"]0,35[/TD]
[TD="class: xl86, width: 37"]0,31[/TD]
[TD="class: xl80, width: 37"]0,39[/TD]
[TD="class: xl81, width: 37"]0,47[/TD]

[TD="class: xl69, width: 72"]3[/TD]
[TD="class: xl79, width: 37"]0,06[/TD]
[TD="class: xl80, width: 37"]0,08[/TD]
[TD="class: xl81, width: 37"]0,09[/TD]
[TD="class: xl86, width: 37"]0,14[/TD]
[TD="class: xl80, width: 37"]0,17[/TD]
[TD="class: xl81, width: 37"]0,2[/TD]
[TD="class: xl86, width: 37"]0,24[/TD]
[TD="class: xl80, width: 37"]0,29[/TD]
[TD="class: xl81, width: 37"]0,35[/TD]
[TD="class: xl86, width: 37"]0,35[/TD]
[TD="class: xl80, width: 37"]0,43[/TD]
[TD="class: xl81, width: 37"]0,52[/TD]
[TD="class: xl86, width: 37"]0,47[/TD]
[TD="class: xl80, width: 37"]0,58[/TD]
[TD="class: xl81, width: 37"]0,7[/TD]

[TD="class: xl69, width: 72"]4[/TD]
[TD="class: xl79, width: 37"]0,22[/TD]
[TD="class: xl80, width: 37"]0,28[/TD]
[TD="class: xl81, width: 37"]0,33[/TD]
[TD="class: xl86, width: 37"]0,32[/TD]
[TD="class: xl80, width: 37"]0,4[/TD]
[TD="class: xl81, width: 37"]0,48[/TD]
[TD="class: xl86, width: 37"]0,47[/TD]
[TD="class: xl80, width: 37"]0,59[/TD]
[TD="class: xl81, width: 37"]0,71[/TD]
[TD="class: xl86, width: 37"]0,66[/TD]
[TD="class: xl80, width: 37"]0,83[/TD]
[TD="class: xl81, width: 37"]0,99[/TD]
[TD="class: xl86, width: 37"]0,94[/TD]
[TD="class: xl80, width: 37"]1,17[/TD]
[TD="class: xl81, width: 37"]1,4[/TD]

[TD="class: xl69, width: 72"]5[/TD]
[TD="class: xl79, width: 37"]0,24[/TD]
[TD="class: xl80, width: 37"]0,3[/TD]
[TD="class: xl81, width: 37"]0,36[/TD]
[TD="class: xl86, width: 37"]0,37[/TD]
[TD="class: xl80, width: 37"]0,46[/TD]
[TD="class: xl81, width: 37"]0,55[/TD]
[TD="class: xl86, width: 37"]0,55[/TD]
[TD="class: xl80, width: 37"]0,68[/TD]
[TD="class: xl81, width: 37"]0,82[/TD]
[TD="class: xl86, width: 37"]0,78[/TD]
[TD="class: xl80, width: 37"]0,97[/TD]
[TD="class: xl81, width: 37"]1,16[/TD]
[TD="class: xl86, width: 37"]1,09[/TD]
[TD="class: xl80, width: 37"]1,36[/TD]
[TD="class: xl81, width: 37"]1,64[/TD]

[TD="class: xl69, width: 72"]6[/TD]
[TD="class: xl79, width: 37"]0,26[/TD]
[TD="class: xl80, width: 37"]0,33[/TD]
[TD="class: xl81, width: 37"]0,39[/TD]
[TD="class: xl86, width: 37"]0,41[/TD]
[TD="class: xl80, width: 37"]0,51[/TD]
[TD="class: xl81, width: 37"]0,62[/TD]
[TD="class: xl86, width: 37"]0,63[/TD]
[TD="class: xl80, width: 37"]0,78[/TD]
[TD="class: xl81, width: 37"]0,94[/TD]
[TD="class: xl86, width: 37"]0,89[/TD]
[TD="class: xl80, width: 37"]1,11[/TD]
[TD="class: xl81, width: 37"]1,34[/TD]
[TD="class: xl86, width: 37"]1,24[/TD]
[TD="class: xl80, width: 37"]1,55[/TD]
[TD="class: xl81, width: 37"]1,86[/TD]

[TD="class: xl69, width: 72"]7[/TD]
[TD="class: xl79, width: 37"]0,28[/TD]
[TD="class: xl80, width: 37"]0,35[/TD]
[TD="class: xl81, width: 37"]0,42[/TD]
[TD="class: xl86, width: 37"]0,46[/TD]
[TD="class: xl80, width: 37"]0,57[/TD]
[TD="class: xl81, width: 37"]0,68[/TD]
[TD="class: xl86, width: 37"]0,71[/TD]
[TD="class: xl80, width: 37"]0,88[/TD]
[TD="class: xl81, width: 37"]1,06[/TD]
[TD="class: xl86, width: 37"]1,01[/TD]
[TD="class: xl80, width: 37"]1,26[/TD]
[TD="class: xl81, width: 37"]1,52[/TD]
[TD="class: xl86, width: 37"]1,4[/TD]
[TD="class: xl80, width: 37"]1,74[/TD]
[TD="class: xl81, width: 37"]2,09[/TD]

[TD="class: xl69, width: 72"]8[/TD]
[TD="class: xl79, width: 37"]0,3[/TD]
[TD="class: xl80, width: 37"]0,38[/TD]
[TD="class: xl81, width: 37"]0,45[/TD]
[TD="class: xl86, width: 37"]0,5[/TD]
[TD="class: xl80, width: 37"]0,63[/TD]
[TD="class: xl81, width: 37"]0,75[/TD]
[TD="class: xl86, width: 37"]0,78[/TD]
[TD="class: xl80, width: 37"]0,98[/TD]
[TD="class: xl81, width: 37"]1,17[/TD]
[TD="class: xl86, width: 37"]1,13[/TD]
[TD="class: xl80, width: 37"]1,41[/TD]
[TD="class: xl81, width: 37"]1,69[/TD]
[TD="class: xl86, width: 37"]1,55[/TD]
[TD="class: xl80, width: 37"]1,94[/TD]
[TD="class: xl81, width: 37"]2,33[/TD]

[TD="class: xl69, width: 72"]9[/TD]
[TD="class: xl79, width: 37"]0,32[/TD]
[TD="class: xl80, width: 37"]0,4[/TD]
[TD="class: xl81, width: 37"]0,48[/TD]
[TD="class: xl86, width: 37"]0,55[/TD]
[TD="class: xl80, width: 37"]0,68[/TD]
[TD="class: xl81, width: 37"]0,82[/TD]
[TD="class: xl86, width: 37"]0,86[/TD]
[TD="class: xl80, width: 37"]1,08[/TD]
[TD="class: xl81, width: 37"]1,29[/TD]
[TD="class: xl86, width: 37"]1,24[/TD]
[TD="class: xl80, width: 37"]1,55[/TD]
[TD="class: xl81, width: 37"]1,86[/TD]
[TD="class: xl86, width: 37"]1,71[/TD]
[TD="class: xl80, width: 37"]2,13[/TD]
[TD="class: xl81, width: 37"]2,56[/TD]

[TD="class: xl69, width: 72"]10[/TD]
[TD="class: xl82, width: 37"]0,34[/TD]
[TD="class: xl83, width: 37"]0,43[/TD]
[TD="class: xl84, width: 37"]0,51[/TD]
[TD="class: xl87, width: 37"]0,59[/TD]
[TD="class: xl83, width: 37"]0,74[/TD]
[TD="class: xl84, width: 37"]0,89[/TD]
[TD="class: xl87, width: 37"]0,94[/TD]
[TD="class: xl83, width: 37"]1,17[/TD]
[TD="class: xl84, width: 37"]1,4[/TD]
[TD="class: xl87, width: 37"]1,36[/TD]
[TD="class: xl83, width: 37"]1,69[/TD]
[TD="class: xl84, width: 37"]2,03[/TD]
[TD="class: xl87, width: 37"]1,86[/TD]
[TD="class: xl83, width: 37"]2,33[/TD]
[TD="class: xl84, width: 37"]2,79[/TD]

</tbody>

On Sheet2 I have above table. On Sheet1 I would like to have (probably) a dropdown menu to specify one value. So you would choose Section, than Selection (witch is inside each Section) and for last value from left bar (from 1 to 10).

Does any one have an idea how to solve this problem.

Thanks, Peter
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
You might need to adjust the overall range here, but...

Code: 
=INDEX(A1:P12,MATCH([Left Bar Reference Cell],A1:A12,0),MATCH([Section Reference Cell]&[Selection Reference Cell],A1:P1&A2:P2,0))
... is working for me.

This is an array formula so you need to enter it with CTRL+SHIFT+ENTER.

Hope this helps,

Chris.

EDIT: I just realised that the merged cells would cause some problems. I would personally solve this by have a section number in every column and hiding the unnecessary ones using the conditional formatting rule...

Code: 
=B1=A1
... with the custom format ";;;", applied from B1 to wherever your table ends.
 
Last edited:
Upvote 0
Maybe this:

Layout

[TABLE="width: 657"]
<tbody>[TR]
[TD="class: xl63, width: 51, bgcolor: transparent"]section
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 40, bgcolor: transparent"]Sheet2
[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]Section
[/TD]
[TD="class: xl63, width: 51, bgcolor: transparent"]Selection
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"]ColA
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"]Result
[/TD]
[TD="class: xl64, width: 40, bgcolor: transparent"]Sheet1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]selection
[/TD]
[TD="class: xl63, bgcolor: transparent"]R1
[/TD]
[TD="class: xl63, bgcolor: transparent"]R2
[/TD]
[TD="class: xl63, bgcolor: transparent"]R3
[/TD]
[TD="class: xl63, bgcolor: transparent"]R1
[/TD]
[TD="class: xl63, bgcolor: transparent"]R2
[/TD]
[TD="class: xl63, bgcolor: transparent"]R3
[/TD]
[TD="class: xl63, bgcolor: transparent"]R1
[/TD]
[TD="class: xl63, bgcolor: transparent"]R2
[/TD]
[TD="class: xl63, bgcolor: transparent"]R3
[/TD]
[TD="class: xl63, bgcolor: transparent"]R1
[/TD]
[TD="class: xl63, bgcolor: transparent"]R2
[/TD]
[TD="class: xl63, bgcolor: transparent"]R3
[/TD]
[TD="class: xl63, bgcolor: transparent"]R1
[/TD]
[TD="class: xl63, bgcolor: transparent"]R2
[/TD]
[TD="class: xl63, bgcolor: transparent"]R3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent"]R2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]1,17
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,02
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,03
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,03
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,05
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,06
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,07
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,08
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,19
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,23
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"]R3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0,33
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,04
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,05
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,06
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,09
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,19
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,23
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,23
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,31
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,47
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"]R1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0,41
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,06
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,08
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,09
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,43
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,52
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,47
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,58
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,7
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,28
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,33
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,32
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,48
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,47
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,59
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,71
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,66
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,83
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,99
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,94
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,36
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,46
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,68
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,82
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,78
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,97
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,09
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,36
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,64
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,33
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,41
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,51
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,62
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,63
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,78
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,94
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,89
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,34
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,86
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,28
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,42
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,46
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,57
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,68
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,71
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,88
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,06
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,01
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,52
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,74
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,09
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,38
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,45
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,63
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,75
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,78
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,98
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,13
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,41
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,69
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,94
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,33
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,32
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,48
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,68
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,82
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,86
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,08
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,86
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,71
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,13
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,56
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,34
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,43
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,51
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,59
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,74
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,89
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,94
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,36
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,69
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,03
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1,86
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,33
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2,79
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]********
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[/TR]
</tbody>[/TABLE]


Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
=IFERROR(INDEX(Sheet2!$B$3:$P$12,$C2,
SMALL(IF(Sheet2!$B$1:$P$1=$A2,COLUMN(Sheet2!$B$3:$P$3)+MATCH($B2,Sheet2!$B$2:$D$2,0)-COLUMN(Sheet2!$B$2)),1)),"")

Or

=IFERROR(INDEX(Sheet2!$B$3:$P$12,$C2,
MATCH($A2,Sheet2!$B$1:$P$1,0)+MATCH($B2,Sheet2!$B$2:$D$2,0)-1),"")


Markmzz
 
Last edited:
Upvote 0
markmzz said:
Code: 
=IFERROR(INDEX(Sheet2!$B$3:$P$12,$C2,
SMALL(IF(Sheet2!$B$1:$P$1=$A2,COLUMN(Sheet2!$B$3:$P$3)+MATCH($B2,Sheet2!$B$2:$D$2,0)-COLUMN(Sheet2!$B$2)),1)),"")

Or

=IFERROR(INDEX(Sheet2!$B$3:$P$12,$C2,
MATCH($A2,Sheet2!$B$1:$P$1,0)+MATCH($B2,Sheet2!$B$2:$D$2,0)-1),"")
Click to expand...

To enter the second formula use only Enter.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

Lacan
  • Question Question
Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?
Replies
2
Views
452
Lacan
Lacan
J
  • Question Question
Finding and summing an interval in a table
Replies
0
Views
488
Julianohcosta
J
S
  • Question Question
Automatically get the x and y values to calculate a SLOPE
2
Replies
16
Views
2K
DILIPandey
DILIPandey
M
  • Question Question
Matching Algorithm
Replies
3
Views
836
oldbrewer
O
M
  • Question Question
Sum Blocks of Data
Replies
4
Views
952
mucah!t
M

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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