Example:

A1 is 1

A2 is 2

A3 is 3

B1 is A

B2 is B

B3 is C

In column C, I have random values in the range 1 to 3 in several rows.

I set up LOOKUP(C1, A1:A3, B1:B3) in cell D1.

The value returned is accurate in D1.

Then I try to drag copy the formula down the remainder of the D column (let's say there are about 20 rows)... but it doesn't work. Excel changes the array range, incrementing each subsequent cell (like D2 through D20) to attempt the lookup in a bogus range (includes cells with no data). For example, for the value in C2, it is checking A2:A4 vs. B2:B4. And so on. I want it to use the cell adjacent (Column D) to the random number (Column C) to return the results of the lookup.

How can I set up a simple lookup that uses the value in the C column to locate a value in a static array table?

Note: I have not tried placing the array being looked up on a different sheet than the column that it is indexing, but it shouldn't matter, should it?

If this issue has been discussed before, please provide a link. Thank you.