Although the formula I posted will return the correct result I made a very
slight change in the logical precedence in how it calculates:
=INDEX(A1:C1,MAX((A1:C3=A10)*COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))
=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))
Let's use this smaller table to see how it works:
......A...B...C
1...1...2...3
2...4...5...6
3...7...8...9
Lookup_value = 6
For the topmost:
=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))
=INDEX(A1:C1
The indexed array is A1:C1. Each element of the array is in a specific
indexed position. A1 is in position 1, B1 is in position 2 and C1 is in
position 3. What we need to do to get the result we're after is tell INDEX
we want the value located at position N of the indexed array. We do that by
calculating this:
MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))
This logical expression will return an array of either TRUE or FALSE:
(A1:C3=A10)
T = TRUE
F = FALSE
A1=6=F;B1=6=F;C1=6=F
A2=6=F;B2=6=F;C2=6=T
A3=6=F;B3=6=F;C3=6=F
These TRUE and FALSE are then multiplied by the column numbers that make up
the table range A:C = columns 1,2,3:
(A1:C3=A10)*(COLUMN(A1:C3))
{F,F,F} * {1,2,3}
{F,F,T} * {1,2,3}
{F,F,F} * {1,2,3}
TRUE multiplied by any number other than 0 = that number
FALSE multiplied by any number = 0
So:
{F,F,F} * {1,2,3}= 0,0,0
{F,F,T} * {1,2,3}= 0,0,3
{F,F,F} * {1,2,3}= 0,0,0
This array is then passed to the MAX function:
MAX({0,0,0;0,0,3;0,0,0}) = 3
The result of MAX is then passed to INDEX and the result of the formula is
the value held in position 3 of the indexed array A1:C1:
=INDEX(A1:C1,3) = C1 = 3
Now comes the confusing part!
The positions of the indexed array are *relative* to the referenced range.
If the indexed array was G27:I27 their *relative* positions would still be
G27 in position 1, H27 in position 2 and I27 in position 3. Where this
matters is in this expression:
These TRUE and FALSE are then multiplied by the column numbers
To make sure we end up with *relative* positions that we can pass to the
INDEX function we have to calculate any offset in the range references.
These TRUE and FALSE are then multiplied by the column numbers
If the table range was G27:I29 their column numbers are:
G = 7
H = 8
I = 9
When these column numbers are multiplied along with the TRUE and FALSE of
the logical expression then we would end up with numbers that do not
coincide with the *relative* positions of the indexed array. So, we need to
convert 7,8,9 to 1,2,3. This is how we do that:
-MIN(COLUMN(G27:I29))+1
COLUMN(G27:I29)-MIN(COLUMN(G27:I29))+1
G = col 7 - 7 = 0 + 1 = 1
H = col 8 - 7 = 1 + 1 = 2
I = col 9 - 7 = 2 + 1 = 3
Now our calculated positions coincide with the positions of the indexed
array.
This same logic applies to to formula for the leftmost as well.
NB: This is also a way to make the formula robust against column/row
insertions.
11.