Two way lookup with smallest reference value

[sanrama]

My current table form is

	A	B	C
1	Week No	Area	Compliance
2	Week 1	Item 1	50.00%
3	Week 1	Item 2	60.00%
4	Week 1	Item 3	70.00%
5	Week 2	Item 1	80.00%
6	Week 2	Item 2	55.00%
7	Week 2	Item 3	66.00%
8	Week 2	Item 5	56.00%
9	Week 3	Item 1	53.00%
10	Week 3	Item 2	88.00%
11	Week 3	Item 3	95.00%
12	Week 3	Item 4	99.00%
13	Week 3	Item 5	100.00%
14	Week 4	Item 1	0.00%
15	Week 4	Item 2	35.00%
16	Week 4	Item 5	99.00%
17	Week 4	Item 6	100.00%

<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> </tbody>

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

 

[EFANYoutube]

try this one
=OFFSET(INDIRECT(CELL("address",INDEX(C2:C17,MATCH(MIN(C2:C17),C2:C17,0)))),0,-1)
returns "Item1"

 

[sanrama]

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 ...........

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Week No	Area	Compliance		week 4	week 3
2​	Week 1	Item 1	50.00%​		Item 1	Item 1
3​	Week 1	Item 2	60.00%​			Item 9
4​	Week 1	Item 3	70.00%​
5​	Week 2	Item 1	80.00%​
6​	Week 2	Item 2	55.00%​
7​	Week 2	Item 3	66.00%​
8​	Week 2	Item 5	56.00%​
9​	Week 3	Item 1	53.00%​
10​	Week 3	Item 2	88.00%​
11​	Week 3	Item 3	95.00%​
12​	Week 3	Item 4	99.00%​
13​	Week 3	Item 5	100.00%​
14​	Week 3	Item 9	53.00%​
15​	Week 4	Item 1	0.00%​
16​	Week 4	Item 2	35.00%​
17​	Week 4	Item 5	99.00%​
18​	Week 4	Item 6	100.00%​

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))),"")

 

[sanrama]

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

Row\Col	A	B	C	D	E	F
1	Week No	Area	Compliance		week 4	week 2
2	Week 1	Item 1	50.00%		Item 1	Item 2
3	Week 1	Item 2	60.00%		Item 2	Item 5
4	Week 1	Item 3	70.00%		Item 5	Item 3
5	Week 2	Item 1	80.00%		Item 6	Item 1
6	Week 2	Item 2	55.00%
7	Week 2	Item 3	66.00%
8	Week 2	Item 5	56.00%
9	Week 3	Item 1	53.00%
10	Week 3	Item 2	88.00%
11	Week 3	Item 3	95.00%
12	Week 3	Item 4	99.00%
13	Week 3	Item 5	100.00%
14	Week 3	Item 9	53.00%
15	Week 4	Item 1	0.00%
16	Week 4	Item 2	35.00%
17	Week 4	Item 5	99.00%
18	Week 4	Item 6	100.00%

<tbody>
</tbody><colgroup><col span="7"></colgroup>

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

 

[Aladin Akyurek]

That's a different question...

Row\Col	A​	B​	C​	D​	E​
1​	Week No	Area	Compliance	week 4	week 3
2​	Week 1	Item 1	50.00%​	Item 1	Item 1
3​	Week 1	Item 2	60.00%​	Item 2	Item 9
4​	Week 1	Item 3	70.00%​	Item 5	Item 2
5​	Week 2	Item 1	80.00%​	Item 6	Item 3
6​	Week 2	Item 2	55.00%​		Item 4
7​	Week 2	Item 3	66.00%​
8​	Week 2	Item 5	56.00%​
9​	Week 3	Item 1	53.00%​
10​	Week 3	Item 2	88.00%​
11​	Week 3	Item 3	95.00%​
12​	Week 3	Item 4	99.00%​
13​	Week 3	Item 5	100.00%​
14​	Week 3	Item 9	53.00%​
15​	Week 4	Item 1	0.00%​
16​	Week 4	Item 2	35.00%​
17​	Week 4	Item 5	99.00%​
18​	Week 4	Item 6	100.00%​

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)))),"")

 

[sanrama]

Exactly what I am looking for


Thank you Aladin Akyurek

 

[Aladin Akyurek]

Exactly what I am looking for


Thank you Aladin Akyurek

You are welcome.