Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up named ranges as an array
=IF(G5="","",VLOOKUP(G5,UoMrev,2,False))
Ensures an exact match. See if that helps. -- Regards, Tom Ogilvy "L.White" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Use named ranges in array formula | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming | |||
Array and Named Ranges | Excel Programming |