Looking up named ranges as an array

Two questions in one day, this is getting ridiculous.

Anyway I have a named range (UoMrev). Columns C and D. Rows 1 to 100.
Everything below row 55 is blank. The range is populated by a SQL query so I
left extra room in case the database grows.

In cell G5 I have copied the value from one of the cells in Column C. In
cell G6 I have the following code:
=IF(G5="","",VLOOKUP(G5,UoMrev,2,1))
In cell G7 I have the following code
=IF(G5="","",LOOKUP(G5,UoMrev))

These two cells do give me the same results. Now here is the data from Cell
C. Complete and in order from 1 to 55.

fcpopval
2GK
5GK
5GA
BLO
BRD
BDF
BOT
BUN
CAN
CRD
CT
CS
CUF
DOZ
GAL
GAK
GRM
.5G
.5P
HR
HND
IN
KT
LCU
LFT
LY
LTR
LOT
MTR
MIL
OZ
PKG
PAD
PR
PNT
PNK
QT
QTK
REA
ROL
SET
SHE
SLE
SCU
SPO
TBS
M
TUB
BAG
EA
BOX
LBS
SQF


The values in column D are the following.

fcpoptext
2 GALLON KIT
5 GALLON KIT
5 GALLON PAIL
BLOCK
BOARD
BOARD FEET
BOTTLE
BUNDLE
CAN
CARD
CARTON
CASE
CUBIC FEET
DOZEN
GALLON
GALLON KIT
GRAMS
HALF GALLON
HALF PINT
HOURS
HUNDREDS
INCHES
KIT
LARGE CUP
LINEAR FEET
LINEAR YARD
LITER
LOT
METER
MILEAGE
OUNCE
PACKAGE
PAD
PAIR
PINT
PINT KIT
QUART
QUART KIT
REAM
ROLL
SET
SHEET
SLEEVE
SMALL CUP
SPOOL
TABLE SPOON
THOUSAND
TUBE
BAG
EA
BOX
POUNDS
SQUARE FEET


Column C is just a group of abbreviations for D. Now, if I place the values
SQF, LBS, or 5GK into cell G5 I get the correct name as a result. If I place
2GK, .5G or .5P in then I get #N/A. If I place any other abbreviation into
G5 I get an incorrect response that varies depending on the chosen value.
Does anyone know why?

The whole point of this is that my users are going to select a material from
a list of materials. When I import the material information from one sheet
to another it brings in the abbreviation. If I place the abreviation in a
different cell I can have the visible cell hold the matching name with a
lookup of some sort.The way the user does not need to remember the
abbreviation and I don't need to worry about converting the abbreviation
supplied by SQL when the query runs.

LWhite

=IF(G5="","",VLOOKUP(G5,UoMrev,2,False))

Ensures an exact match.

See if that helps.

--
Regards,
Tom Ogilvy


"L.White" <[email protected]> wrote in message
news:%[email protected]...
> Two questions in one day, this is getting ridiculous.
>
> Anyway I have a named range (UoMrev). Columns C and D. Rows 1 to 100.
> Everything below row 55 is blank. The range is populated by a SQL query so
I
> left extra room in case the database grows.
>
> In cell G5 I have copied the value from one of the cells in Column C. In
> cell G6 I have the following code:
> =IF(G5="","",VLOOKUP(G5,UoMrev,2,1))
> In cell G7 I have the following code
> =IF(G5="","",LOOKUP(G5,UoMrev))
>
> These two cells do give me the same results. Now here is the data from
Cell
> C. Complete and in order from 1 to 55.
>
> fcpopval
> 2GK
> 5GK
> 5GA
> BLO
> BRD
> BDF
> BOT
> BUN
> CAN
> CRD
> CT
> CS
> CUF
> DOZ
> GAL
> GAK
> GRM
> .5G
> .5P
> HR
> HND
> IN
> KT
> LCU
> LFT
> LY
> LTR
> LOT
> MTR
> MIL
> OZ
> PKG
> PAD
> PR
> PNT
> PNK
> QT
> QTK
> REA
> ROL
> SET
> SHE
> SLE
> SCU
> SPO
> TBS
> M
> TUB
> BAG
> EA
> BOX
> LBS
> SQF
>
>
> The values in column D are the following.
>
> fcpoptext
> 2 GALLON KIT
> 5 GALLON KIT
> 5 GALLON PAIL
> BLOCK
> BOARD
> BOARD FEET
> BOTTLE
> BUNDLE
> CAN
> CARD
> CARTON
> CASE
> CUBIC FEET
> DOZEN
> GALLON
> GALLON KIT
> GRAMS
> HALF GALLON
> HALF PINT
> HOURS
> HUNDREDS
> INCHES
> KIT
> LARGE CUP
> LINEAR FEET
> LINEAR YARD
> LITER
> LOT
> METER
> MILEAGE
> OUNCE
> PACKAGE
> PAD
> PAIR
> PINT
> PINT KIT
> QUART
> QUART KIT
> REAM
> ROLL
> SET
> SHEET
> SLEEVE
> SMALL CUP
> SPOOL
> TABLE SPOON
> THOUSAND
> TUBE
> BAG
> EA
> BOX
> POUNDS
> SQUARE FEET
>
>
> Column C is just a group of abbreviations for D. Now, if I place the
values
> SQF, LBS, or 5GK into cell G5 I get the correct name as a result. If I
place
> 2GK, .5G or .5P in then I get #N/A. If I place any other abbreviation into
> G5 I get an incorrect response that varies depending on the chosen value.
> Does anyone know why?
>
> The whole point of this is that my users are going to select a material
from
> a list of materials. When I import the material information from one sheet
> to another it brings in the abbreviation. If I place the abreviation in a
> different cell I can have the visible cell hold the matching name with a
> lookup of some sort.The way the user does not need to remember the
> abbreviation and I don't need to worry about converting the abbreviation
> supplied by SQL when the query runs.
>
> LWhite
>
>