Index table lookup anomaly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with a table lookup. This is the formula I use:
=INDEX('PRODUCT TABLE'!$E$2:$M$146,MATCH(L23,'PRODUCT TABLE'!$E$2:$E$145,0),9)

It returns a correct response (or 0) for every lookup except MR! If I
change the L23 cell to MP I get the correct response for MP. If I change it
back to MR, I get #N/A. I have changed the Product Table cells for MR -
brought the cell above (MP) down to the MR cell and changed it to MR, added
and deleted the other lookup fields in the table hoping to get a response -
nothing. I've deleted the MR rows and added them back in - nothing.

Any ideas besides changing the MR code to something else? I don't want to
do that since the MR is the logical code to use.

Thanks,

Carole O
 
Hi
and there're no blanks in your lookup table for the 'MR' value?
 
Thanks Frank for your help. Here is part of the table I'm using (it
stretches to one row (A - M)
CODE CATEGORY PPR DESCRIPTION PC COLOR/SIDE Press Sheet
Size STOCK BLEED UP # ITEMS BINDERY IND STD SHEETS/PKG UNIT
CAT 2-MAKE READY MP Make new plates MP
CORP 2-MAKE READY MP Make new plates MP
CUST 2-MAKE READY MP Make new plates MP
CAT 2-MAKE READY MR Make Ready MR
CORP 2-MAKE READY MR Make Ready MR
CUST 2-MAKE READY MR Make Ready MR
 
Hi
if you like email me your file with the non-working formula
 
Frank -
I have a file all ready for you - what is your e-mail address?

(e-mail address removed)

Carole
 
Hi, Frank -

Here's an update. I inserted 3 lines above MP, moved MP to the new lines,
re-entered MR, and deleted the MR lines. Guess what - it works!! There must
have been an unprintable character in those lines.

Thanks again for your interest and help.

Carole

Frank Kabel said:
Hi
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Carole O said:
Frank -
I have a file all ready for you - what is your e-mail address?

(e-mail address removed)

Carole
Click to expand...
Click to expand...
 
Hi
yes, you had spaces in these fields :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Carole O said:
Hi, Frank -

Here's an update. I inserted 3 lines above MP, moved MP to the new lines,
re-entered MR, and deleted the MR lines. Guess what - it works!! There must
have been an unprintable character in those lines.

Thanks again for your interest and help.

Carole

Frank Kabel said:
Hi
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Frank -
I have a file all ready for you - what is your e-mail address?

(e-mail address removed)

Carole

:

Hi
if you like email me your file with the non-working formula

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks Frank for your help. Here is part of the table I'm
Click to expand...
using
(it
stretches to one row (A - M)
CODE CATEGORY PPR DESCRIPTION PC COLOR/SIDE Press Sheet
Size STOCK BLEED UP # ITEMS BINDERY IND STD SHEETS/PKG UNIT
CAT 2-MAKE READY MP Make new plates MP
CORP 2-MAKE READY MP Make new plates MP
CUST 2-MAKE READY MP Make new plates MP
CAT 2-MAKE READY MR Make Ready MR
CORP 2-MAKE READY MR Make Ready MR
CUST 2-MAKE READY MR Make Ready MR




:

Hi
and there're no blanks in your lookup table for the 'MR' value?

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
I have a spreadsheet with a table lookup. This is the formula I
use:
=INDEX('PRODUCT TABLE'!$E$2:$M$146,MATCH(L23,'PRODUCT
TABLE'!$E$2:$E$145,0),9)

It returns a correct response (or 0) for every lookup
Click to expand...
except
MR!
If
I
change the L23 cell to MP I get the correct response for
Click to expand...
MP.
If
I
change it
back to MR, I get #N/A. I have changed the Product Table cells
for
MR -
brought the cell above (MP) down to the MR cell and
Click to expand...
changed
it to
MR,
added
and deleted the other lookup fields in the table hoping
Click to expand...
to
get a
response -
nothing. I've deleted the MR rows and added them back in -
nothing.

Any ideas besides changing the MR code to something else? I
don't
want to
do that since the MR is the logical code to use.

Thanks,

Carole O
Click to expand...
Click to expand...
Click to expand...
 
Thanks again, Frank. Just one more question, how can I tell if there are
spaces in the fields?

Carole

Frank Kabel said:
Hi
yes, you had spaces in these fields :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Carole O said:
Hi, Frank -

Here's an update. I inserted 3 lines above MP, moved MP to the new lines,
re-entered MR, and deleted the MR lines. Guess what - it works!! There must
have been an unprintable character in those lines.

Thanks again for your interest and help.

Carole

Frank Kabel said:
Hi
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Frank -
I have a file all ready for you - what is your e-mail address?

(e-mail address removed)

Carole

:

Hi
if you like email me your file with the non-working formula

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
Thanks Frank for your help. Here is part of the table I'm using
(it
stretches to one row (A - M)
CODE CATEGORY PPR DESCRIPTION PC COLOR/SIDE Press Sheet
Size STOCK BLEED UP # ITEMS BINDERY IND STD SHEETS/PKG UNIT
CAT 2-MAKE READY MP Make new plates MP
CORP 2-MAKE READY MP Make new plates MP
CUST 2-MAKE READY MP Make new plates MP
CAT 2-MAKE READY MR Make Ready MR
CORP 2-MAKE READY MR Make Ready MR
CUST 2-MAKE READY MR Make Ready MR




:

Hi
and there're no blanks in your lookup table for the 'MR' value?

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
I have a spreadsheet with a table lookup. This is the
formula I
use:
=INDEX('PRODUCT TABLE'!$E$2:$M$146,MATCH(L23,'PRODUCT
TABLE'!$E$2:$E$145,0),9)

It returns a correct response (or 0) for every lookup except
MR!
If
I
change the L23 cell to MP I get the correct response for MP.
If
I
change it
back to MR, I get #N/A. I have changed the Product Table
cells
for
MR -
brought the cell above (MP) down to the MR cell and changed
it to
MR,
added
and deleted the other lookup fields in the table hoping to
get a
response -
nothing. I've deleted the MR rows and added them back in -
nothing.

Any ideas besides changing the MR code to something else? I
don't
want to
do that since the MR is the logical code to use.

Thanks,

Carole O
Click to expand...
Click to expand...
Click to expand...
 
Hi
one way:
=ISNUMBER(SEARCH(" ",A1))

--
Regards
Frank Kabel
Frankfurt, Germany

Carole O said:
Thanks again, Frank. Just one more question, how can I tell if there are
spaces in the fields?

Carole

Frank Kabel said:
Hi
yes, you had spaces in these fields :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi, Frank -

Here's an update. I inserted 3 lines above MP, moved MP to the
Click to expand...
new
lines,
re-entered MR, and deleted the MR lines. Guess what - it works!! There must
have been an unprintable character in those lines.

Thanks again for your interest and help.

Carole

:

Hi
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Frank -
I have a file all ready for you - what is your e-mail address?

(e-mail address removed)

Carole

:

Hi
if you like email me your file with the non-working formula

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
Thanks Frank for your help. Here is part of the table
Click to expand...
I'm
using
(it
stretches to one row (A - M)
CODE CATEGORY PPR DESCRIPTION PC COLOR/SIDE Press Sheet
Size STOCK BLEED UP # ITEMS BINDERY IND STD SHEETS/PKG UNIT
CAT 2-MAKE READY MP Make new plates MP
CORP 2-MAKE READY MP Make new plates MP
CUST 2-MAKE READY MP Make new plates MP
CAT 2-MAKE READY MR Make Ready MR
CORP 2-MAKE READY MR Make Ready MR
CUST 2-MAKE READY MR Make Ready MR




:

Hi
and there're no blanks in your lookup table for the
Click to expand...
'MR'
value?
--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
I have a spreadsheet with a table lookup. This is the
formula I
use:
=INDEX('PRODUCT TABLE'!$E$2:$M$146,MATCH(L23,'PRODUCT
TABLE'!$E$2:$E$145,0),9)

It returns a correct response (or 0) for every lookup except
MR!
If
I
change the L23 cell to MP I get the correct response
Click to expand...
for
MP.
If
I
change it
back to MR, I get #N/A. I have changed the Product Table
cells
for
MR -
brought the cell above (MP) down to the MR cell and changed
it to
MR,
added
and deleted the other lookup fields in the table
Click to expand...
hoping
to
get a
response -
nothing. I've deleted the MR rows and added them
Click to expand...
back
in -
nothing.

Any ideas besides changing the MR code to something
Click to expand...
else?
I
don't
want to
do that since the MR is the logical code to use.

Thanks,

Carole O
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help - Trouble with counting occurrences in external file 2
vlookup and match first value greater than 4
Pivot Table - Problems when using Calculated Item 1
MS-ACCESS : How to get a final result of [lookup table] which field is <string_variable> ? 0
duplicate name help please 9
Lookup returning wrong results 0
Pivot table column help 2
Lookup related question 1

Back
Top