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

[i_am_chip]

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.

 

[Aladin Akyurek]

Try...

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

 

[T. Valko]

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)

 

[i_am_chip]

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?

 

[Aladin Akyurek]

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.

 

[T. Valko]

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.