If Function Problem

Hi, I apologise if this question is really simple/stupid, but i am a beginner at excel. So
I want to get a cell to display the value of another cell. and i am trying to do this with a range. Here is the formula:

Formula:

Please Login or Register  to view this content.

The values in Data2! are the ones that i am trying to display.
CA, CL, NCA, NCL, and E are my ranges.
D1 is a value in the ranges
D1 is a value that will constantly change.

But my formula gives a #Value! everytime and i am not sure what i am doing wrong.

Thank you to anyone who can help me with my problem.

Try

=IF($D$1="CA",Data2!$D27,IF($D$1="CL",Data2!$D39,IF($D$1="NCL",Data2!$D55,IF($D$1="NCA",Data2!$D17,IF($D$1="E",Data2!$D66,"Error")))))

CA is a range
D1 is a cell
A cell can never be equal to a range.
Please post a xls file

Please remove this reply, because i do not know how

Is this what you meant?
Change the yellow cells and see what happend with the orange cells.

Thanks a lot that is exactly what I needed.

You have no idea how long I have been trying to come up with a solution for this.

You really saved my life.

But, if i may, i have one last question. It is about the formula, it is really confusing. For the sake of others who might be viewing this thread and for my own, i was hoping you would give an explanation. Also to prevent myself from asking these kinds of questions again and feeling like

Formula:

Please Login or Register  to view this content.

Thanks again

In vlookup formula you use a range to seach in,
If you give different ranges different names then you can use the indirect formule
The indirect formula point to a cell with a name, now you can quickly change the different ranges