Two way lookup with smallest reference value

S

sanrama

New Member
Joined
May 2, 2015
Messages
23
Office Version
  1. 2013
Platform
  1. Windows
My current table form is
[TABLE="width: 203"]
<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;"> <col width="150" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5485;"> <tbody>[TR]
[TD="class: xl64, width: 69, bgcolor: steelblue, align: center"][/TD]
[TD="class: xl64, width: 69, bgcolor: steelblue, align: center"]A[/TD]
[TD="class: xl64, width: 50, bgcolor: steelblue, align: center"]B[/TD]
[TD="class: xl64, width: 150, bgcolor: steelblue, align: center"] C[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 69, bgcolor: steelblue"]1[/TD]
[TD="class: xl64, width: 69, bgcolor: steelblue"]Week No[/TD]
[TD="class: xl64, width: 50, bgcolor: steelblue"]Area[/TD]
[TD="class: xl64, width: 150, bgcolor: steelblue"]Compliance[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 69, bgcolor: #F7F6F3"]2[/TD]
[TD="class: xl65, width: 69, bgcolor: #F7F6F3"]Week 1[/TD]
[TD="class: xl65, width: 50, bgcolor: #F7F6F3"]Item 1[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]50.00%[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 69, bgcolor: #F7F6F3"]3[/TD]
[TD="class: xl65, width: 69, bgcolor: #F7F6F3"]Week 1[/TD]
[TD="class: xl65, width: 50, bgcolor: #F7F6F3"]Item 2[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]60.00%[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 69, bgcolor: #F7F6F3"]4[/TD]
[TD="class: xl65, width: 69, bgcolor: #F7F6F3"]Week 1[/TD]
[TD="class: xl65, width: 50, bgcolor: #F7F6F3"]Item 3[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]70.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]5[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 2[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 1[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]80.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]6[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 2[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 2[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]55.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]7[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 2[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 3[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]66.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]8[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 2[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 5[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]56.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]9[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 3[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 1[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]53.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]10[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 3[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 2[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]88.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]11[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 3[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 3[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]95.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]12[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 3[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 4[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]99.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]13[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 3[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 5[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]100.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]14[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 4[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 1[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]15[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 4[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 2[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]35.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]16[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 4[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 5[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]99.00%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 69, bgcolor: white"]17[/TD]
[TD="class: xl66, width: 69, bgcolor: white"]Week 4[/TD]
[TD="class: xl66, width: 50, bgcolor: white"]Item 6[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]

I requred following value

reference value ( from Column B ) from smallest value (column C) in week 4

Lookup value 1 : week 4 ( column A )
Lookup value 2 : smallest in week 4 from column C
Answer : Item 1
 

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
try this one
=OFFSET(INDIRECT(CELL("address",INDEX(C2:C17,MATCH(MIN(C2:C17),C2:C17,0)))),0,-1)
returns "Item1"
 
Upvote 0
Thanks EFANYoutube,

but it's not working

This file contain large no data.
Lookup value 1 is variable week 1, week 2, week 3 .......
Lookup value 2 is 1st smallest value for week 1, 2nd smallest value for week 1, 3rd ...........
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#4682B4"]Week No[/td][td="bgcolor:#4682B4"]Area[/td][td="bgcolor:#4682B4"]Compliance[/td][td][/td][td]week 4[/td][td]week 3[/td][/tr]
[tr][td]
2​
[/td][td="bgcolor:#F7F6F3"]Week 1[/td][td="bgcolor:#F7F6F3"]Item 1[/td][td]
50.00%​
[/td][td][/td][td]Item 1[/td][td]Item 1[/td][/tr]
[tr][td]
3​
[/td][td="bgcolor:#F7F6F3"]Week 1[/td][td="bgcolor:#F7F6F3"]Item 2[/td][td]
60.00%​
[/td][td][/td][td][/td][td]Item 9[/td][/tr]
[tr][td]
4​
[/td][td="bgcolor:#F7F6F3"]Week 1[/td][td="bgcolor:#F7F6F3"]Item 3[/td][td]
70.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
5​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 1[/td][td]
80.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 2[/td][td]
55.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 3[/td][td]
66.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 5[/td][td]
56.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 1[/td][td]
53.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 2[/td][td]
88.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 3[/td][td]
95.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
12​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 4[/td][td]
99.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
13​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 5[/td][td]
100.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
14​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 9[/td][td]
53.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
15​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 1[/td][td]
0.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
16​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 2[/td][td]
35.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
17​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 5[/td][td]
99.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
18​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 6[/td][td]
100.00%​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$18,SMALL(IF($A$2:$A$18=E$1,IF($C$2:$C$18=MIN(IF($A$2:$A$18=E$1,$C$2:$C$18)),ROW($B$2:$B$18)-ROW($B$2)+1)),ROWS(E$2:E2))),"")
 
Upvote 0
Thank you aladin akyurek,

but it is woking only for 1st smallest value or smallest duplicate value, not for 2nd/3rd smallest value for the same week

My requirement is

[TABLE="width: 735"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Week No[/TD]
[TD]Area[/TD]
[TD]Compliance[/TD]
[TD] [/TD]
[TD]week 4[/TD]
[TD]week 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Week 1[/TD]
[TD]Item 1[/TD]
[TD]50.00%[/TD]
[TD] [/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Week 1[/TD]
[TD]Item 2[/TD]
[TD]60.00%[/TD]
[TD] [/TD]
[TD]Item 2[/TD]
[TD]Item 5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Week 1[/TD]
[TD]Item 3[/TD]
[TD]70.00%[/TD]
[TD] [/TD]
[TD]Item 5[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Week 2[/TD]
[TD]Item 1[/TD]
[TD]80.00%[/TD]
[TD] [/TD]
[TD]Item 6[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Week 2[/TD]
[TD]Item 2[/TD]
[TD]55.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Week 2[/TD]
[TD]Item 3[/TD]
[TD]66.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Week 2[/TD]
[TD]Item 5[/TD]
[TD]56.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Week 3[/TD]
[TD]Item 1[/TD]
[TD]53.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Week 3[/TD]
[TD]Item 2[/TD]
[TD]88.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Week 3[/TD]
[TD]Item 3[/TD]
[TD]95.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Week 3[/TD]
[TD]Item 4[/TD]
[TD]99.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Week 3[/TD]
[TD]Item 5[/TD]
[TD]100.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Week 3[/TD]
[TD]Item 9[/TD]
[TD]53.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Week 4[/TD]
[TD]Item 1[/TD]
[TD]0.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Week 4[/TD]
[TD]Item 2[/TD]
[TD]35.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Week 4[/TD]
[TD]Item 5[/TD]
[TD]99.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Week 4[/TD]
[TD]Item 6[/TD]
[TD]100.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="7"></colgroup>[/TABLE]


set of data contain 75 weeks and each week is having 10-15 item
Week no is changing as per requirement in E1/F/ cell
By changing only week no I suppose to get particular week data in ascending or descending.


Thank you in advance
 
Upvote 0
That's a different question...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#4682B4"]Week No[/td][td="bgcolor:#4682B4"]Area[/td][td="bgcolor:#4682B4"]Compliance[/td][td]week 4[/td][td]week 3[/td][/tr]
[tr][td]
2​
[/td][td="bgcolor:#F7F6F3"]Week 1[/td][td="bgcolor:#F7F6F3"]Item 1[/td][td]
50.00%​
[/td][td]Item 1[/td][td]Item 1[/td][/tr]
[tr][td]
3​
[/td][td="bgcolor:#F7F6F3"]Week 1[/td][td="bgcolor:#F7F6F3"]Item 2[/td][td]
60.00%​
[/td][td]Item 2[/td][td]Item 9[/td][/tr]
[tr][td]
4​
[/td][td="bgcolor:#F7F6F3"]Week 1[/td][td="bgcolor:#F7F6F3"]Item 3[/td][td]
70.00%​
[/td][td]Item 5[/td][td]Item 2[/td][/tr]
[tr][td]
5​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 1[/td][td]
80.00%​
[/td][td]Item 6[/td][td]Item 3[/td][/tr]
[tr][td]
6​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 2[/td][td]
55.00%​
[/td][td][/td][td]Item 4[/td][/tr]
[tr][td]
7​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 3[/td][td]
66.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td="bgcolor:#F7F6F3"]Week 2[/td][td]Item 5[/td][td]
56.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 1[/td][td]
53.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 2[/td][td]
88.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 3[/td][td]
95.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
12​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 4[/td][td]
99.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
13​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 5[/td][td]
100.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
14​
[/td][td="bgcolor:#F7F6F3"]Week 3[/td][td]Item 9[/td][td]
53.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
15​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 1[/td][td]
0.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
16​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 2[/td][td]
35.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
17​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 5[/td][td]
99.00%​
[/td][td][/td][td][/td][/tr]
[tr][td]
18​
[/td][td="bgcolor:#F7F6F3"]Week 4[/td][td]Item 6[/td][td]
100.00%​
[/td][td][/td][td][/td][/tr]
[/table]

In D2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$18,SMALL(IF($A$2:$A$18=D$1,IF($C$2:$C$18=SMALL(IF($A$2:$A$18=D$1,$C$2:$C$18),ROWS($D$2:D2)),ROW($C$2:$C$18)-ROW($C$2)+1)),SUM(IF(SMALL(IF($A$2:$A$18=D$1,$C$2:$C$18),ROW($C$2:C2)-ROW($C$2)+1)=SMALL(IF($A$2:$A$18=D$1,$C$2:$C$18),ROWS(D$2:D2)),1)))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Edit working code to look for value after a date & before a date
Replies
6
Views
116
Alex Blakenburg
A
S
  • Question Question
VBA, OLAP cube pivot : refer to parent of selected filter?
Replies
1
Views
526
srgexcel
S
S
  • Question Question
Edit listbox values including columns and update the worksheet
Replies
0
Views
286
spanner28
S
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
P
  • Question Question
How to find till which row the marco executed. It stopped at some random row without any error and unable to identitfy at what row it has been stopped
Replies
3
Views
187
FormR
FormR

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

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