3 Way lookup

[reyjey]

Hi,

So I have been reading the forum trying to help me with a 3 way look up, but I have been having some problems. I think I have a good base, but I cant seem to get my formula correct. I have 3 variables I would need to sort through column A and B and then through row 6 (this row)


A B C D E F G H I J K L M
50% 30% 10% 8% 6% 5% 4% 3% 2% 1% 0.50%
5% ----- 6 10 30 38 50 60 75 100 150 300 600
5% 0.10 8 13 37 46 62 74 92 123 184 368 736
5% 0.20 10 16 47 58 78 93 116 155 232 463 925
5% 0.30 12 20 60 75 100 120 150 200 300 600 1,199
5% 0.40 17 27 81 102 135 162 203 270 405 809 1,618
5% 0.50 24 39 116 145 193 231 289 385 577 1,154 2,308
10% ----- 5 8 24 29 39 47 58 77 116 231 461
10% 0.20 7 12 35 43 57 69 86 114 171 341 682
10% 0.30 9 15 44 55 73 87 109 145 217 433 866
10% 0.40 12 20 58 72 96 115 143 191 286 572 1,144
10% 0.50 16 27 80 100 134 160 200 267 400 799 1,597
15% ---- 4 7 19 24 32 38 48 64 95 190 380
15% 0.20 6 10 28 35 46 55 69 91 137 273 545
15% 0.30 7 12 35 43 57 69 86 114 171 341 681
15% 0.40 9 15 45 56 74 89 111 148 221 442 883
15% 0.50 13 21 61 76 101 121 151 202 302 604 1,208
20% ---- 4 6 17 21 27 33 41 54 81 161 322
20% 0.20 5 8 23 29 38 46 57 76 113 226 451
20% 0.30 6 10 28 35 47 56 70 93 139 277 554
20% 0.40 8 12 36 45 59 71 89 118 177 354 707
20% 0.50 10 16 48 60 80 95 119 159 238 475 949
25% ---- 3 5 14 18 24 28 35 47 70 139 278
25% 0.20 4 7 19 24 32 38 48 64 95 190 380
25% 0.30 5 8 23 29 39 46 58 77 115 230 460
25% 0.40 6 10 29 37 49 58 73 97 145 289 578
25% 0.50 8 13 38 48 64 76 95 127 190 380 760
30% ---- 3 5 13 16 21 25 31 41 61 121 241
30% 0.20 4 6 17 21 27 33 41 54 81 162 323
30% 0.40 5 8 24 30 40 48 60 80 120 239 477
30% 0.60 9 15 43 54 71 85 107 142 213 425 850
35% ---- 3 4 11 14 18 21 27 35 53 105 210
35% 0.20 3 5 14 18 23 28 35 46 69 138 276
35% 0.40 4 7 20 25 34 40 50 67 100 199 397
35% 0.60 7 12 34 43 57 68 85 113 169 338 676
50% ---- 2 3 7 9 12 14 18 24 35 70 139
50% 0.20 2 3 9 11 15 18 22 29 44 87 173
50% 0.40 3 4 12 15 19 23 29 38 57 114 228
50% 0.60 4 6 17 22 29 34 43 57 85 170 340


This is the formula I used.
=INDEX(C5:M44,MATCH(P29,B7:B44,0)+MATCH(P30,A7:A44,0),MATCH(P28,C6:M6,0))

So pretty much I need help trying to correct this formula.

Thanks,

 

[xld]

Try this array formula

=INDEX(C5:M44,MATCH(1,(P29=B7:B44)*(P30=A7:A44)*(P28=C6:M6),0))

 

[reyjey]

I tried the formula, but it's giving me a #value error. Did I have to change the formula in anyway?

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX($C$7:$M$44,MATCH(1,IF($A$7:$A$44=P30,IF($B$7:$B$44=P29,1)),0),MATCH(P28,$C$6:$M$6,-1))

 

[Matty]

Hello reyjey,

Did you commit xld's formula by pressing Ctrl+Shift+Enter?

This has to be done as it's an array formula...

Matty

 

[reyjey]

Thanks, it works.

Saved me a lot of time. I would have pulled out all my hair by the time I figured what was wrong.

 

[Matty]

Hello Aladin,

Control+shift+enter, not just enter:

=INDEX($C$7:$M$44,MATCH(1,IF($A$7:$A$44=P30,IF($B$7:$B$44=P29,1)),0),MATCH(P28,$C$6:$M$6,-1))

What is the match_type of -1 doing in the column_num of INDEX? I'm confused!

Thanks,

Matty

 

[Matty]

...also, is this construct considered the best (fastest) for this kind of lookup, unless one is prepared to set up concatenated helper Columns?

Thanks,

Matty

 

[Aladin Akyurek]

Hello Aladin,



What is the match_type of -1 doing in the column_num of INDEX? I'm confused!

Thanks,

Matty

C6:M6 is sorted in descending order, so the formula takes that into account.

 

[Aladin Akyurek]

...also, is this construct considered the best (fastest) for this kind of lookup, unless one is prepared to set up concatenated helper Columns?

Thanks,

Matty

Hard to tell where and when a gain in efficiency can be obtained. Too many concatenation formulas (say, 50,000) must also affect efficiency adversely.