Hi!
3. the array formula would be in a column inserted between cols A and B and
copied by dragging down the first entered cell.
If that's the case then your data is in the range C:F
Entered in B2 as an array with the key combo of CTRL,SHIFT,ENTER:
=INDEX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0))
Copied down returns:
B2 = R
B3 = T
B4 = K
B5:B15 = #N/A
If you want to suppress the display of #N/A:
=IF(ISNA(MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)),"",IND EX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)))
OR, use the shorter formula together with conditional formatting:
Select the range B2:B15
Conditional Formatting
Formula is: =ISNA(B2)
Set the font color to be the same as the background color.
OK out
Biff
"z.entropic" wrote in message
...
I re-wrote my example to clarify it even mo
A B C D E
1 4
2 a a 1 o
3 b a 2 p
4 c a 3 q
5 d a 4 r
6 e
7 f b 2 s
8 g b 4 t
9 h b 3 u
10 i b 1 v
11 j
12 k c 5 x
13 l c 1 y
14 m c 2 z
15 n c 4 k
In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a
VLOOKUP in two columns at the same time, where one value (B1) is a
constant).
The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4).
z.entropic
|