Lookup Function not doing what I want

[nova110488]

I am trying to enter this formula across 5000 cells, the problem I get when trying to drag the formula down is not only the RED is changing, the lookup vector and result vector are also increasing (BLUE). Is there any way I can create a macro to enter this for me, or adjust the way excel modifies the formula?

What is happening:

=LOOKUP('Full Report'![B][COLOR=red]D2[/COLOR][/B],'Data Legend'![COLOR=blue][B]A2:A16[/B][/COLOR],'Data Legend'![COLOR=blue][B]B2:B16[/B][/COLOR])
=LOOKUP('Full Report'![COLOR=red][B]D3[/B][/COLOR],'Data Legend'![COLOR=blue][B]A3:A17[/B][/COLOR],'Data Legend'![COLOR=blue][B]B3:B17[/B][/COLOR])
=LOOKUP('Full Report'![COLOR=red][B]D4[/B][/COLOR],'Data Legend'![COLOR=blue][B]A4:A18[/B][/COLOR],'Data Legend'![COLOR=blue][B]B4:B18[/B][/COLOR])

What I want:

=LOOKUP('Full Report'![B][COLOR=red]D2[/COLOR][/B],'Data Legend'![COLOR=blue][B]A2:A16[/B][/COLOR],'Data Legend'![COLOR=blue][B]B2:B16[/B][/COLOR])
=LOOKUP('Full Report'![COLOR=red][B]D3[/B][/COLOR],'Data Legend'![COLOR=blue][B]A2:A16[/B][/COLOR],'Data Legend'![COLOR=blue][B]B2:B16[/B][/COLOR])
=LOOKUP('Full Report'![COLOR=red][B]D4[/B][/COLOR],'Data Legend'![COLOR=blue][B]A2:A16[/B][/COLOR],'Data Legend'![COLOR=blue][B]B2:B16[/B][/COLOR])

Thanks

 

[VoG]

Try

=LOOKUP('Full Report'!D2,'Data Legend'!A$2:A$16,'Data Legend'!B$2:B$16)

 

[nova110488]

Perfect, thanks for the help

 

[HalfAce]

Hi and welcome to the board.
I'm not completely sure I understand what you're asking, but I think you're wanting to copy the formulas and not have them adjust their cell references relative to where you're pasting them. (yes?)

If you have the following formula:

=LOOKUP('Full Report'!D2,'Data Legend'!A2:A16,'Data Legend'!B2:B16)

and wanted to paste it somewhere, having the cell references not update to the new location you would put a dollar sign ($) in front of the row & column references. (This makes them absolute references instead of the relative references being used now.)

EDIT:
I see Vog got here while I was interrupted posting my response.