3 Way lookup

reyjey

New Member
Joined
Nov 16, 2009
Messages
3
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,
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try this array formula

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

reyjey

New Member
Joined
Nov 16, 2009
Messages
3
I tried the formula, but it's giving me a #value error. Did I have to change the formula in anyway?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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

New Member
Joined
Nov 16, 2009
Messages
3
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
...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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
...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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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
Top