| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Index table lookup anomaly
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 |
|
#2
|
|||
|
|||
|
Hi
and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#3
|
|||
|
|||
|
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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#4
|
|||
|
|||
|
Hi
if you like email me your file with the non-working formula -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#6
|
|||
|
|||
|
Hi
frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Frank - I have a file all ready for you - what is your e-mail address? Carole "Frank Kabel" wrote: Hi if you like email me your file with the non-working formula -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#7
|
|||
|
|||
|
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" wrote: Hi frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Frank - I have a file all ready for you - what is your e-mail address? Carole "Frank Kabel" wrote: Hi if you like email me your file with the non-working formula -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#8
|
|||
|
|||
|
Hi
yes, you had spaces in these fields :-) -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... 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" wrote: Hi frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Frank - I have a file all ready for you - what is your e-mail address? Carole "Frank Kabel" wrote: Hi if you like email me your file with the non-working formula -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#9
|
|||
|
|||
|
Thanks again, Frank. Just one more question, how can I tell if there are
spaces in the fields? Carole "Frank Kabel" wrote: Hi yes, you had spaces in these fields :-) -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... 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" wrote: Hi frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Frank - I have a file all ready for you - what is your e-mail address? Carole "Frank Kabel" wrote: Hi if you like email me your file with the non-working formula -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
|
#10
|
|||
|
|||
|
Hi
one way: =ISNUMBER(SEARCH(" ",A1)) -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Thanks again, Frank. Just one more question, how can I tell if there are spaces in the fields? Carole "Frank Kabel" wrote: Hi yes, you had spaces in these fields :-) -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... 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" wrote: Hi frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im Newsbeitrag ... Frank - I have a file all ready for you - what is your e-mail address? Carole "Frank Kabel" wrote: Hi if you like email me your file with the non-working formula -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 "Frank Kabel" wrote: Hi and there're no blanks in your lookup table for the 'MR' value? -- Regards Frank Kabel Frankfurt, Germany "Carole O" schrieb im 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Table | Excel Worksheet Functions | |||
| How to use a table index from a formula | Excel Worksheet Functions | |||
| Lookup value in colA whos row matches row of index value in colB | Excel Worksheet Functions | |||
| Lookup value in colA whos row matches row of index value in colB | Excel Worksheet Functions | |||
| double lookup, nest, or macro? | Excel Worksheet Functions | |||
Linear Mode
