Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
I created a Pivot table, and did a copy/paste special values to get rid of
the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
What could cause this?
Using a match_type argument of 1 (or TRUE or omitted) when the data is not sorted in ascending order. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
Yeah, I tried that. I just keep getting the last number in the index list.
I've under Index/Match thousands of times before; can't figure out why it's not working now. Anything else? Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "T. Valko" wrote: What could cause this? Using a match_type argument of 1 (or TRUE or omitted) when the data is not sorted in ascending order. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
Ryan; have a close look at the zipcodes (Press F2) to see if there are any
other characters present in the array data. If this post helps click Yes --------------- Jacob Skaria "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
Hmmm...
If you have the match_type set for an exact match then the formula should return #N/A if an exact match isn't found. Can I see your file (if it's not too big: 1mb)? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Yeah, I tried that. I just keep getting the last number in the index list. I've under Index/Match thousands of times before; can't figure out why it's not working now. Anything else? Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "T. Valko" wrote: What could cause this? Using a match_type argument of 1 (or TRUE or omitted) when the data is not sorted in ascending order. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
Hi,
Why not show us your formula and some sample data? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
xl can help at comcast period net
Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Biff, what is your email? I tried the one @comcast but it bounced back to me. Please send me an email. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: Hi, Why not show us your formula and some sample data? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
Problem solved. It was due to a misplaced closing parenthesis.
-- Biff Microsoft Excel MVP "T. Valko" wrote in message ... xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Biff, what is your email? I tried the one @comcast but it bounced back to me. Please send me an email. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: Hi, Why not show us your formula and some sample data? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
I have to ask, why wouldn't you show the formula to all of us?
-- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: Biff, what is your email? I tried the one @comcast but it bounced back to me. Please send me an email. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: Hi, Why not show us your formula and some sample data? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
Evidentially, I used this type of function:
=INDEX(range,MATCH(cell,range),0) This caused excel to default to 1 (TRUE). Should have been: =INDEX(range,MATCH(cell,range,0)) By do I feel dumb... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: I have to ask, why wouldn't you show the formula to all of us? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: Biff, what is your email? I tried the one @comcast but it bounced back to me. Please send me an email. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: Hi, Why not show us your formula and some sample data? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/Match Misbehaving
By do I feel dumb...
Welcome to the club. Been there, done that! -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Evidentially, I used this type of function: =INDEX(range,MATCH(cell,range),0) This caused excel to default to 1 (TRUE). Should have been: =INDEX(range,MATCH(cell,range,0)) By do I feel dumb... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: I have to ask, why wouldn't you show the formula to all of us? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: Biff, what is your email? I tried the one @comcast but it bounced back to me. Please send me an email. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Shane Devenshire" wrote: Hi, Why not show us your formula and some sample data? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ryguy7272" wrote: I created a Pivot table, and did a copy/paste special values to get rid of the Pivot Table. Now, I try to do a simple Index/Match, and I almost always get the lowest value in the list as a result, no matter what lookup value I use for the Match. What could cause this? I know the zip codes (this is the array for the index) come out as text (this file was downloaded from an external system). That shouldn't matter, right. I'm not concerned about the data type, I'm just looking for my Match, but can't find it. Anyway, even if I convert the zips to number-type, the results are still wrong. Any ideas? Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |