List of Codes and Display Names for Codes

  • Thread starter Thread starter hoganc
  • Start date Start date
H

hoganc

hi all,

last week I asked the question could i look up values in a sheet an
the answer is yes

only thing is trying to be clever I extended th
=VLOOKUP(A1,Sheet2!A:B,2,FALSE)

and grabbed the right hand curser and pulled right effectively draggin
the code to the right. However the code has now slipped by 3 places

ie


it looks up value

C803/3530 =VLOOKUP(A1,Sheet2!A:B,2,FALSE)
it should show

C803/3530 Team Name

cos i pulled it to the right it now shows

C803/3530 Team name Paul McWhatever
only thing is Paul mc whatever is for the the name of the person
places away on the sheet no. system so if C803/ 3530 is A5 and team i
A5 then the name of person displayed is A2

i dont know how that happened but does it mean they are all out??
 
Sounds to me you have an issue with a "Relative" cell reference, whe
you should have an "Absolute" reference. This is accomplished b
putting a "$" in front of the Column and/or Cell portion of you
formula:

$A$1=always refers to cell A1
$A1 = always refers to Column A, with the cell relative to current row
A$1 = always refers to Row 1, with the cell relative to the curren
column
(enter "relative reference" into Excel Help for more)

in your example, try
=VLOOKUP(A1,Sheet2!$A:$B,2,FALSE)

This will look up the value in the current cell, but retain the looku
table reference to columns A and B on Sheet2.

Another way around this is to use a named range. Highlight the desire
range (in your example on sheet2, click and drag on the 'A' to the 'B
column headers to highlight both full columns). From the menu, selec
Insert>Name>Define... Give this a distinctive name, e.g. "mylist" an
click OK. Your formula could now be written as:

=VLOOKUP(A1,Sheet2!mylist,2,FALSE)

Good Luck
 
Back
Top