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

i_am_chip

New Member
Joined
May 29, 2012
Messages
2
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.
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top