Using the Small function to update corresponding values

[virencm]

Hello,

You guys provided me with the answer to the attached problem i had last time.

I was wondering if you could help if I added another dimension to this spreadsheet.

So the intial problem i had was to find the first smallest and second smallest values and paste corresponding in table below.... This has been solved.

What i now have is a Value that needs to be checked before we add the secondsmallest , 3rd smallest....

I have explained it in the attached spreadsheet. Its a complex one...

Please take a look at the spreadsheet and let me know if you need any further information.

Cheers
Viren

[Bo_Ry]

Please try at B44 drag across and down

=IFERROR(INDEX($A:$A,MOD(AGGREGATE(15,6,B$2:B$26*100+ROW(B$2:B$26)/(B$2:B$26<MIN(B$2:B$26)+$L$3)/(B$2:B$26>0),ROWS(B$44:B44)),100)),"")

[virencm]

Excellent!!! Thank You B0_Ry.

Can you please explain how the formula works?

[Bo_Ry]

A. B$2:B$26*100+ROW(B$2:B$26) Value multiply by 100 and row number, divide by

B. /(b$2:b$26<min(b$2:b$26)+$l$3)/(b$2:b$26>0) for check value and it less than Minimum + L3 and more than zero

A/B = A/True = A and A/False = Div/0

C. Aggregate(15 for small, 6 ignore error Div/0, value array, k) give small value of rank k( 1,2, …) eg 1331305 ,1331306
gren part is row number

D. Mod(C, 100) gives row number and

Index($A:$A,D) gives the final result

[virencm]

Thank you.

Really appreciate it.

[virencm]

One last thing thing Bo_Ry,

In row 80 and below can you please create a formula to list all parts that were not listed in the rows 44 -73?

Thanks

Viren

[Bo_Ry]

Please try at B80

=IFERROR(INDEX($A:$A,MOD(AGGREGATE(15,6,B$2:B$26*100+ROW(B$2:B$26)/(B$2:B$26>=MIN(B$2:B$26)+$L$3),ROWS(B$80:B80)),100)),"")