How to use a formula to drag down, listing top 10 smallest results, from matrix
Requirement:
The user has a matrix with addresses on the left, and places across the top. Within the matrix are numbers (distances in km). On Sheet 2, the user would like to compile lists for each address, showing the names of the 10 closest Places, sorted from shortest to furthest.
Solution:
AliGW provided the following solution:
In B2 drag copied down:
Code:
=INDEX(Table4[[#Headers],[Place 1]:[Place 225]],MATCH(SMALL(OFFSET(Table4[[#Headers],[Address]],MATCH(Sheet2!B$1,Table4[Address],0),3,1,100),ROWS(B$2:B2)),OFFSET(Table4[[#Headers],[Address]],MATCH(Sheet2!B$1,Table4[Address],0),3,1,100),0))
Then block copy and paste across for each of the other columns.
JonathanVH provided the following solution:
Code:
=INDEX(Table4[#All],1,MATCH(SMALL(OFFSET(Sheet1!$E$3:$HU$3,COLUMN(B$1)-2,0),ROW($A1)),OFFSET(Sheet1!$E$3:$HU$3,COLUMN(B$1)-2,0),0)+4)
Please be aware that if there are two (or more) values returned that are identical, the formula will return the same (first) Place found for each instead of all the Places.You could work around this by making it a much more complex array formula (that checks values above itself).
Obtained from the OzGrid Help Forum.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a macro or formula to copy data from cell to all cells in that group in adjacent column |
| How to copy entire row - keeping formulas |
| How to use Drop down lists and conditional formulas |
| How to convert split formula in VBA in their respective columns |
| How to create a button to run formula on every sheet in workbook |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
