CODEWITHSUNDEEP
arraysexcelsortingexcel-formulavlookup
David T
David T

Reputation: 31

Table sort and lookup

I have an excel table (25x25) which looks like this,

    C1  C2  C3
R1  5   6   7
R2  1   7   9
R3  2   3   0

my goal is to make it look like this,

C3  R3  0
C1  R2  1
C1  R3  2
C2  R3  3
C1  R1  5
C2  R1  6
C2  R2  7
C3  R1  7
C3  R2  9

It generates a new table ranked by the values in the first. It also tells the corresponding column and row name.The table has duplicates, negatives and decimals.

I'm doing this because I'd like to find the 3 closest candidates (and hence the C's and R's) of a given value. And VLOOKUP() requires a sorted table.

Another problem (a step forward) is that VLOOKUP() returns the closest smaller value instead of actually the smallest. Is there a better way to do it or a workaround? So that the result is a neat table like such,

Value to look up = 2.8
>> C2 R3 3
>> C1 R3 2
>> C1 R1 5

For some reasons I cannot use VBA for this project. Any solutions with just built-in functions in MS Excel?

Upvotes: 3

Views: 76

Answers (1)

user4039065
user4039065

Reputation:

If you need to use only native worksheet functions, this can be accomplished; even without array formulas.

        flatten_matrix

With your original data in A1:D4, the formulas in F3:H3 are,

=INDEX(B$1:D$1, AGGREGATE(15, 6, COLUMN($A:$C)/(B$2:D$4=H3), COUNTIF(H$3:H3, H3)))
=INDEX(A$2:A$4, AGGREGATE(15, 6, ROW($1:$3)/(B$2:D$4=H3), COUNTIF(H$3:H3, H3)))
=SMALL(B$2:D$4,ROW(1:1))

Fill down as necessary.

The formulas in K5:N5 are,

=INDEX(B$1:D$1, AGGREGATE(15, 6, COLUMN($A:$C)/(B$2:D$4=M5), COUNTIF(M$5:M5, M5)))
=INDEX(A$2:A$4, AGGREGATE(15, 6, ROW($1:$3)/(B$2:D$4=H3), COUNTIF(M$5:M5, M5)))
=IF(COUNTIF($B$2:$D$4, N5+$K$2)>=COUNTIF(N$5:N5, N5), N5+$K$2, $K$2-N5)
=AGGREGATE(15,6,ABS($B$2:$D$4-$K$2),ROW(1:1))

Fill down as necessary.

I've included enough rows in the K5:N13 matrix that you can see how the two 7 values are handled.

Upvotes: 2

Related Questions