What you did is first select the range C13:C17 then applied the formula on this range as one would do with formula returning arrays.
In fact this formula does NOT RETURN an array, but only a value.
This implies that the RC-2 does not adapt and stays looking for the same value thus giving the same result everywhere
In this case, you should enter the formula ONLY in C13, commit it with Ctrl+Shift+Enter in the formula bar and THEN pull it down.

Your use of RC notation also obscures the fact that the reference to EMPL ID changes on each row. RC-2 visually stays the same when pulled down, so, one would not see that the cell reference stayed unchanged in your example.

To be honest, I am active on several forums since many years and have NEVER seen anyone using the RC notation in formula, except of course for VBA, and I would assume that if it was more efficient to do so, lots of MVP ' s would, but, they don't.

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Pecoflyer, thanks for pointing me in this direction. I took this long to figure out how to make it work, not because your directions were wrong or even unclear but because I had to keep fooling with them them, many different ways ("why does it work when I do this but not when I do this? They seem equivalent"). The point, as you may already understand, was not for me to make it work, but to understand how it works.

I'm still having trouble with it, because sometimes I can make array arithmetic work without <Ctrl-Shift-Enter> and sometimes not. In the latter cases, the formula bar claims that it's working correctly (that the MATCH function is returning 2, for example), yet what's actually displayed in the worksheet is #N/A—unless I use <Ctrl-Shift-Enter>. I don't suppose you know why, by any chance?

I would need an example to see what you mean. How does the formula bar claim it's working?

Also take into account that some functions create arrays to calculate a single answer (SUMPRODUCT or COLUMNS), but return an array which can only be seen by selecting a range before entering the formula as you did ( see the INDEX(range,,#) syntax)

Perhaps this introduction to array formulas will shed some more light on the matter

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Well, I'm trying to form a complete mental picture of how this works. I started with some formulae that manipulate arrays without <Ctrl-Shift-Enter>:These display 3, 9.424777961 and 1.5, respectively. So far, so good. Next sequence:These all display 2.

What I've established so far, you see, is that it isn't necessary to use <Ctrl-Shift-Enter> in order to do array arithmetic. But sometimes it is. When is it necessary, and when isn't it? That's what I'm trying to work out. Next I created a 20-row table with =ROW()*5 in column 1 (5, 10, 15...) and =RC1+2 in column 2 (7, 12, 17...), and tried these:These display 10, 12 and 120; all correct.Woops! Now something's changed. The first one displays 3, as expected, but the other two, which I thought also would display 3, instead say #VALUE!—an error code, by the way, which isn't mentioned in MATCH's documentation.

But why? When I supply "manual" arrays to INDEX and MATCH, with and without array manipulation, they work fine. And INDEX works, also, when I substitute an array range for a "manual" array, but MATCH chokes as soon as I try array arithmetic with it. What's the difference? And why does MATCH care about that, but not INDEX?

As for your question ("How does the formula bar claim it's working?"), take a look at the attached screen shot. I selected the second MATCH function, the one that used the range*3, then clicked on the fx button next to the formula bar, and this is what came up. It shows that Excel is correctly interpreting each argument of the MATCH function, that the result of the array arithmetic is correct ({15;30;45...}) and even that the result of the MATCH function is 3. But what's actually displayed is not the result of the MATCH, but #VALUE!.

When I use <Ctrl-Shift-Enter> with those last two, they work fine.

In case you want to see the original, and perhaps convert back to A1 notation if you don't trust R1C1, I've attached the workbook; see Sheet2 for all the samples.

Attached Images

x.jpg (50.0 KB, 10 views)

Attached Files

x.xlsx (14.1 KB, 9 views)