MATCH() searching in a list of numbers

Posted by Chris II on March 23, 2001 10:40 AM

I have a column with a list of catalog numbers and a corresponding column with the item's name. I am using INDEX(MATCH()) to look in the column of catalog numbers and return the item's name. The problem I am having is when the catalog number is all numbers and no letters, MATCH() will not find it. WHY!?! The formula I'm using works with all other entries but those will all numbers. I'm looking for an alternative method or an error I may have in my formula.

FYI:
-Catalog numbers are not in the far left column and cannot be, so cannot use VLOOKUP().
-Have headings for the columns.
-Have named the cell ranges, but also tried defining the range instead of using the named range (didn't work).
-Information is being copied within the same worksheet.

Formula I'm Using:
=INDEX(EEIC_TITLE,MATCH(N2,EEIC_CODE,0))

EEIC_TITLE = named range of the column containing the catalog item names
N2 = cell that contains the catalog item number to lookup
EEIC_CODE = named range of the column containing the catalog numbers

Posted by Ian on March 23, 2001 10:49 AM

One possibility

Is it possible that numbers and text values could be getting mixed. On your master sheet, try doing a text to columns under the data menu and press finish. It could be text in that spot and when you copy it is turning into numbers?

Goof luck

Posted by Aladin Akyurek on March 23, 2001 11:51 AM

Care to provide a small portion of your data for which you use INDEX/MATCH-formula?

Posted by Dave Hawley on March 23, 2001 8:20 PM

Hi Chris

i have just tried and I'm not able to reproduce the problem. But try this anyway.

Select cell N2 and go to Data>Validation and select "List" the put: =EEIC_Code

DaveOzGrid Business Applications