dyanmic lookup function?

chris_streeter

New Member
Joined
Apr 10, 2014
Messages
22
Dear all,

I am trying to find a formula that will return a value in the "Value" column in the small table to the right of the larger table. i.e next to cat 1 ref 129 should have a value of 182,500?

Can anyone help?

Thanks

Chris




[TABLE="width: 896"]
<tbody>[TR]
[TD]Cat 1
[/TD]
[TD]Cat 1
[/TD]
[TD]Cat 1
[/TD]
[TD]Cat 2
[/TD]
[TD]Cat 2
[/TD]
[TD]Cat 2
[/TD]
[TD]Cat 3
[/TD]
[TD]Cat 3
[/TD]
[TD]Cat 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cat
[/TD]
[TD]Ref
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]Ref
[/TD]
[TD]Value
[/TD]
[TD]Status
[/TD]
[TD]Ref
[/TD]
[TD]Value
[/TD]
[TD]Status
[/TD]
[TD]Ref
[/TD]
[TD]Value
[/TD]
[TD]Status
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cat 1
[/TD]
[TD="align: right"]129
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]247,500
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]368,000
[/TD]
[TD][/TD]
[TD]145
[/TD]
[TD]365,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cat 3
[/TD]
[TD="align: right"]258
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124
[/TD]
[TD]186,240
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]360,000
[/TD]
[TD][/TD]
[TD]146
[/TD]
[TD]324,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cat 2
[/TD]
[TD="align: right"]16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]125
[/TD]
[TD]189,500
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]320,000
[/TD]
[TD][/TD]
[TD]147
[/TD]
[TD]314,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]126
[/TD]
[TD]242,600
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]320,000
[/TD]
[TD][/TD]
[TD]148
[/TD]
[TD]325,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]127
[/TD]
[TD]244,500
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]312,000
[/TD]
[TD][/TD]
[TD]149
[/TD]
[TD]348,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]128
[/TD]
[TD]236,000
[/TD]
[TD][/TD]
[TD]6
[/TD]
[TD]261,000
[/TD]
[TD][/TD]
[TD]150
[/TD]
[TD]249,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]129
[/TD]
[TD]182,500
[/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]305,000
[/TD]
[TD][/TD]
[TD]151
[/TD]
[TD]247,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]130
[/TD]
[TD]192,000
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]249,995
[/TD]
[TD][/TD]
[TD]152
[/TD]
[TD]259,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]131
[/TD]
[TD]275,000
[/TD]
[TD][/TD]
[TD]9
[/TD]
[TD]249,995
[/TD]
[TD][/TD]
[TD]153
[/TD]
[TD]189,450
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]132
[/TD]
[TD]274,500
[/TD]
[TD][/TD]
[TD]10
[/TD]
[TD]255,000
[/TD]
[TD][/TD]
[TD]154
[/TD]
[TD]191,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]133
[/TD]
[TD]185,000
[/TD]
[TD][/TD]
[TD]11
[/TD]
[TD]249,995
[/TD]
[TD][/TD]
[TD]155
[/TD]
[TD]264,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]134
[/TD]
[TD]249,995
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]260,000
[/TD]
[TD][/TD]
[TD]258
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]135
[/TD]
[TD]254,500
[/TD]
[TD][/TD]
[TD]13
[/TD]
[TD]264,995
[/TD]
[TD][/TD]
[TD]259
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]136
[/TD]
[TD]184,400
[/TD]
[TD][/TD]
[TD]14
[/TD]
[TD]268,495
[/TD]
[TD][/TD]
[TD]260
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]137
[/TD]
[TD]229,500
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]199,875
[/TD]
[TD][/TD]
[TD]261
[/TD]
[TD]265,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]138
[/TD]
[TD]184,500
[/TD]
[TD][/TD]
[TD]16
[/TD]
[TD]239,495
[/TD]
[TD][/TD]
[TD]262
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]139
[/TD]
[TD]241,500
[/TD]
[TD][/TD]
[TD]17
[/TD]
[TD]192,995
[/TD]
[TD][/TD]
[TD]263
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]140
[/TD]
[TD]274,000
[/TD]
[TD][/TD]
[TD]18
[/TD]
[TD]269,697
[/TD]
[TD][/TD]
[TD]264
[/TD]
[TD]265,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]141
[/TD]
[TD]289,995
[/TD]
[TD][/TD]
[TD]19
[/TD]
[TD]266,500
[/TD]
[TD][/TD]
[TD]265
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]142
[/TD]
[TD]349,995
[/TD]
[TD][/TD]
[TD]20
[/TD]
[TD]339,995
[/TD]
[TD][/TD]
[TD]266
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]143
[/TD]
[TD]299,450
[/TD]
[TD][/TD]
[TD]21
[/TD]
[TD]315,900
[/TD]
[TD][/TD]
[TD]267
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]144
[/TD]
[TD]354,995
[/TD]
[TD][/TD]
[TD]22
[/TD]
[TD]339,000
[/TD]
[TD][/TD]
[TD]268
[/TD]
[TD]210,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]156
[/TD]
[TD]234,495
[/TD]
[TD][/TD]
[TD]23
[/TD]
[TD]329,000
[/TD]
[TD][/TD]
[TD]269
[/TD]
[TD]242,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]157
[/TD]
[TD]189,450
[/TD]
[TD][/TD]
[TD]24
[/TD]
[TD]168,550
[/TD]
[TD][/TD]
[TD]270
[/TD]
[TD]196,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]158
[/TD]
[TD]189,450
[/TD]
[TD][/TD]
[TD]25
[/TD]
[TD]346,000
[/TD]
[TD][/TD]
[TD]271
[/TD]
[TD]270,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]159
[/TD]
[TD]139,500
[/TD]
[TD][/TD]
[TD]26
[/TD]
[TD]185,000
[/TD]
[TD][/TD]
[TD]272
[/TD]
[TD]160,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]160
[/TD]
[TD]234,995
[/TD]
[TD][/TD]
[TD]27
[/TD]
[TD]244,500
[/TD]
[TD][/TD]
[TD]273
[/TD]
[TD]158,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]161
[/TD]
[TD]189,450
[/TD]
[TD][/TD]
[TD]28
[/TD]
[TD]200,000
[/TD]
[TD][/TD]
[TD]274
[/TD]
[TD]264,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]162
[/TD]
[TD]186,500
[/TD]
[TD][/TD]
[TD]29
[/TD]
[TD]200,000
[/TD]
[TD][/TD]
[TD]275
[/TD]
[TD]192,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]163
[/TD]
[TD]186,900
[/TD]
[TD][/TD]
[TD]30
[/TD]
[TD]252,000
[/TD]
[TD][/TD]
[TD]276
[/TD]
[TD]149,495
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]164
[/TD]
[TD]181,500
[/TD]
[TD][/TD]
[TD]31
[/TD]
[TD]250,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]165
[/TD]
[TD]187,000
[/TD]
[TD][/TD]
[TD]32
[/TD]
[TD]215,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]166
[/TD]
[TD]241,500
[/TD]
[TD][/TD]
[TD]33
[/TD]
[TD]218,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]167
[/TD]
[TD]244,995
[/TD]
[TD][/TD]
[TD]34
[/TD]
[TD]205,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]168
[/TD]
[TD]189,500
[/TD]
[TD][/TD]
[TD]35
[/TD]
[TD]209,995
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]169
[/TD]
[TD]192,000
[/TD]
[TD][/TD]
[TD]36
[/TD]
[TD]275,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]170
[/TD]
[TD]239,995
[/TD]
[TD][/TD]
[TD]72
[/TD]
[TD]217,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]171
[/TD]
[TD]230,000
[/TD]
[TD][/TD]
[TD]80
[/TD]
[TD]240,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]172
[/TD]
[TD]234,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]173
[/TD]
[TD]192,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]174
[/TD]
[TD]187,220
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi, something like this maybe?


Excel 2013/2016
ABCDEFGHIJKLM
1Cat 1Cat 1Cat 1Cat 2Cat 2Cat 2Cat 3Cat 3Cat 3CatRefValue
2RefValueStatusRefValueStatusRefValueStatus
3123247,5001368,000145365,000Cat 1129182500
4124186,2402360,000146324,500Cat 3258270000
5125189,5003320,000147314,000Cat 216239495
6126242,6004320,000148325,000
7127244,5005312,000149348,000
8128236,0006261,000150249,500
9129182,5007305,000151247,500
10130192,0008249,995152259,500
11131275,0009249,995153189,450
12132274,50010255,000154191,500
13133185,00011249,995155264,500
14134249,99512260,000258270,000
15135254,50013264,995259270,000
16136184,40014268,495260270,000
17137229,50015199,875261265,000
18138184,50016239,495262270,000
19139241,50017192,995263270,000
20140274,00018269,697264265,000
21141289,99519266,500265270,000
22142349,99520339,995266270,000
23143299,45021315,900267270,000
24144354,99522339,000268210,000
25156234,49523329,000269242,000
26157189,45024168,550270196,500
27158189,45025346,000271270,000
28159139,50026185,000272160,000
29160234,99527244,500273158,500
30161189,45028200,000274264,500
31162186,50029200,000275192,500
32163186,90030252,000276149,495
33164181,50031250,000
34165187,00032215,000
35166241,50033218,000
36167244,99534205,000
37168189,50035209,995
38169192,00036275,000
39170239,99572217,500
40171230,00080240,000
41172234,500
42173192,000
43174187,220
Sheet2
Cell Formulas
RangeFormula
M3=INDEX($A$3:$I$43,MATCH(L3,INDEX($A$3:$I$43,0,MATCH(K3,$A$1:$I$1,0)),0),MATCH(K3,$A$1:$I$1,0)+1)
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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