vlooup will not work correctly if lookup table is on another sheet

  • Thread starter Thread starter Tonso
  • Start date Start date
T

Tonso

i have an XL2003 workbook with mnay sheets. on each sheet i have a
look table. i would like to have the tabl eon just 1 sheet, but here
is the problem. Vlookup will work fine in most cases, however, column
1 of the table consist of single letters, such a G, C, M, T, g, c. If
the lookup table is on the actvie sheet, then if i enter a G, it
lookups up the row with G in the 1st column. If I enter g, then it
returns the row for g. But, if the lookup table is on another sheet, I
will get the row for G when i enter g, instead of the row for g. This
happens with no agument, or TRUE, or FALSE. I have tried using a named
range, or entering the Range as B5:G28. What am I missing?

As always, thanks.

Tonso
 
After serious thinking Tonso wrote :
i have an XL2003 workbook with mnay sheets. on each sheet i have a
look table. i would like to have the tabl eon just 1 sheet, but here
is the problem. Vlookup will work fine in most cases, however, column
1 of the table consist of single letters, such a G, C, M, T, g, c. If
the lookup table is on the actvie sheet, then if i enter a G, it
lookups up the row with G in the 1st column. If I enter g, then it
returns the row for g. But, if the lookup table is on another sheet, I
will get the row for G when i enter g, instead of the row for g. This
happens with no agument, or TRUE, or FALSE. I have tried using a named
range, or entering the Range as B5:G28. What am I missing?

As always, thanks.

Tonso
Click to expand...

Whenusing VLOOKUP/HLOOKUP with tables on other sheets you must specify
the sheetname in the formula something like this...

=VLOOKUP("G",OtherSheetName!TableNameOrRangeAddress,3,FALSE)
 
i have an XL2003 workbook with mnay sheets. [....] Vlookup will
work fine in most cases, however, column 1 of the table consist
of single letters, such a G, C, M, T, g, c. If the lookup table is on
the actvie sheet, then if i enter a G, it lookups up the row with G
in the 1st column. If I enter g, then it returns the row for g. But,
if the lookup table is on another sheet, I will get the row for G when
i enter g, instead of the row for g. This happens with no agument,
or TRUE, or FALSE. I have tried using a named range, or entering
the Range as B5:G28.  What am I missing?
Click to expand...

Well, a concrete example showing __exactly__ what is in the table row-
by-row, column-by-column, and __exactly__ how you are using VLOOKUP.

If a range is given a defined name (e.g. Table), it should have the
form Sheet1!$B$5:$G$28. Then VLOOKUP("g",Table,2) will work on any
worksheet in the workbook.

If you refer to the range directly, you must write VLOOKUP("g",Sheet1!
B5:G28,2).

(You might need to use absolute or mixed references if you intend to
copy the formula.)

However, the fact that you think there is a difference between
VLOOKUP("g",...) and VLOOKUP("G",...) suggests to me that you have not
read the VLOOKUP help page carefully. So you might making other usage
errors.

In particular, when you write that the "table consist of single
letters, such a G, C, M, T, g, c", I wonder if that is the order of
the rows in B5:B28.

If so, you must use FALSE or zero in the 4th parameter of VLOOKUP
because B5:B28 is not in ascending order. If you omit the 4th
parameter or use TRUE, the results may seem unpredictable.

But even if you use FALSE or zero, VLOOKUP does not make a distinction
between "G" and "g". VLOOKUP("G",...,0) and VLOOKUP("g",...,0) will
return the same result corresponding to whichever row appears first in
B5:B28.
 
After serious thinking Tonso wrote :

Whenusing VLOOKUP/HLOOKUP with tables on other sheets you must specify
the sheetname in the formula something like this...

=VLOOKUP("G",OtherSheetName!TableNameOrRangeAddress,3,FALSE)
Click to expand...


Not if you are using a named range.
 
CellShocked laid this down on his screen :
Not if you are using a named range.
Click to expand...

Yes, if the named range is local to the sheet where the table is
stored. Since it's considered best practice to use global scope for
named ranges only when absolutely necessary, local scope should be used
by default. In this case, specifying the sheetname is mandatory.
 
CellShocked laid this down on his screen :

Yes, if the named range is local to the sheet where the table is
stored. Since it's considered best practice to use global scope for
named ranges only when absolutely necessary, local scope should be used
by default. In this case, specifying the sheetname is mandatory.
Click to expand...

That's odd. I make calls to named ranges on other sheets all the time
by simply declaring the name. Perhaps the moment I do that, the range
automatically switches? Because I do not remember specifically making
any special type of 'named range' either.
 
After serious thinking CellShocked wrote :
That's odd. I make calls to named ranges on other sheets all the time
by simply declaring the name. Perhaps the moment I do that, the range
automatically switches? Because I do not remember specifically making
any special type of 'named range' either.
Click to expand...

Then those named ranges MUST have global scope. Otherwise, the
sheetname must be included in your formulas. IOW, if you just typed the
name into the namebox WITHOUT prefacing it with the sheetname then the
name has global scope, and so can be directly referenced on any sheet
in the workbook.

Global names have precedence over local names in references, so even if
you create a local name of the same name on another sheet the global
name will always be used in formulas even if sheetname!localname is
specified.
 
Back
Top