Array formulas

[zt121]

Hello,

I am doing an online course on excel. I am really stuck on one question. I can solve the problem with index, match but the exam requires me to use a single array formula. I am attaching the screenshot of the question, a screenshot of the necessary tables and if needed the whole file. please give me a solution.....Thanks.

 

[StephenCrump]

Welcome to the Forum!

Given you have Excel 365, this exam question is out-of-date for you. The question is asking for an array-entered formula. Whereas with Excel 365, you can do this with a single cell formula that spills into the range I21:M25.

Here's a hint: in older versions of Excel, if you wanted I21:M25 to be the same as I6:M10, you'd need to:

- Select the range I21:M25
- Type the formula =I6:M10 and array-enter it, i.e. CTRL-SHIFT-ENTER rather than just ENTER.

To answer the question, you need to attach the specified row/column conditions to this formula.

	H	I	J	K	L	M
5		A	B	C	D	E
6	A	0	20	55	40	90
7	B	20	0	25	70	35
8	C	55	25	0	10	20
9	D	40	70	10	0	5
10	E	90	35	20	5	0
11
12
13
14
15
16
17
18
19
20		A	B	C	D	E
21	A	0	20	55	40	90
22	B	20	0	25	70	35
23	C	55	25	0	10	20
24	D	40	70	10	0	5
25	E	90	35	20	5	0
Sheet1

Cell Formulas
Range	Formula
I21:M25	I21	=I6:M10
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that in Excel 365, you can simply enter a single cell formula:

I21: =I6:M10