# A lookup that should be easy but is not working as planned

#### i_am_chip

##### New Member
Trying to use LOOKUP to return values.
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try...

=LOOKUP(C1,\$A\$1:\$A\$3,\$B\$1:\$B\$3)

Trying to use LOOKUP to return values.
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.
Try it like this...

=LOOKUP(C1,A\$1:B\$3)

Perfect.

I recognize that the formula you furnished used a relative expression. I know little of using these in practice and may have a need to understand its full potential. Do you know of the section in Excel Help that contains details of how to use these sort of expressions or can you suggest a tutorial or topic that provides that?

Perfect.

I recognize that the formula you furnished used a relative expression. I know little of using these in practice and may have a need to understand its full potential. Do you know of the section in Excel Help that contains details of how to use these sort of expressions or can you suggest a tutorial or topic that provides that?

Google or any other search engine would help to find a tutorial on the internet.

Also, do a search in Excel's help with "Switch between relative, absolute, and mixed references" or just "absolute copying" as search terms.

Perfect.

I recognize that the formula you furnished used a relative expression. I know little of using these in practice and may have a need to understand its full potential. Do you know of the section in Excel Help that contains details of how to use these sort of expressions or can you suggest a tutorial or topic that provides that?
Search Excel help for: relative references.

One of the topics that will be listed is: Switch between relative, absolute, and mixed references.

Replies
4
Views
210
Replies
2
Views
347
Replies
7
Views
336
Replies
3
Views
350
Replies
1
Views
175

1,212,095
Messages
6,105,930
Members
447,984
Latest member
imrics

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back