MS Excel Lookup formula 1

[jcarrott]

Column D contains my old department number and I want the new department number to end up in column E. The conversion is old department in T2:T91 and the new department in V2:V91.
This formula works for the first row:

=LOOKUP(D2,T2:T91,V2:V91)

If I copy the formula from the second row to the third row I get

=LOOKUP(D3,T3:T92,V3:V92

I have 14,300 rows and I need a way to copy the formula so that only column D changes it's value. I want the ranges of T2:T91 and V2:V91 to remain the same.

Can this be done?

 

[AnotherHiggins]

Have a look in Excel's help file about Absolute References vs. Relative References.

Your formula currently uses only Relative References, meaning that as you copy/fill the formula down, the Row & Column References change too.

That is often very handy. In fact that's just the behavior you want in the "D2" part of your formula.

Absolute References are represented by dollar signs ($) in front of the Row And/Or Column portion of a cell reference.

Examples:[ul]
[li]=A2 is completely Relative; both the Row & Column will increment as you fill the formula left/right or up/down[/li]
[li]=$A2 will lock the column to A but will allow the row to increment[/li]
[li]=A$2 will lock the row to 2 but will allow the column to increment[/li]
[li]=$A$2 is completely Absolute; neither the Row nor the Column will increment[/li]
[/ul]

Change your formula to:
[tab][COLOR=blue white]=LOOKUP(D2, $T$2:$T$91, $V$2:$V$91)[/color]
or
[tab][COLOR=blue white]=VLookup(D2, $T$2:$V$91, 0)[/color]

TIP: Selecting a portion of the formula (such as "T2", "T2:T91", or "T2:T91,V2:V91") & pressing [F4] repeatedly will cycle through the different Absolute/Relative combinations

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jcarrott]

Great response - thank you. It works great and I understand why.

Thank you

 

[AnotherHiggins]

Happy to help


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.