Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Function Help
Hi all,
I have a peculiar problem with a function. I am using Excel 2007. I am working with about 57,000 records. I must match the rate of an audio call based on two conditions. I am using the following formula against a list that is sorted in ascending order: =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) I can find a match for all but 600 or so records. There is a match, without a doubt for the other 600. I have made sure that the text the lookup is based on is clean...no trailing or leading spaces. I have even cleared the cells and retyped the data just as it is in the lookup table. No matter how many times, I write it or how I modify the function, those records return 0. I have also updated Excel, ran diagnostics, copied the data to a new workbook, opened and repaired...I just do not know what else to try. Of course, the report is due Friday morning. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Function Help
No matter how many times, I write it or how I
modify the function, those records return 0. =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) If your formula is returning 0 that means the MATCH functions *have found* matches of the lookup values. If they weren't finding matches then one or the other (or possibly both) MATCH function would cause a #N/A error. A 0 could indicate that the cell is empty or it actually does contain a numeric 0. Or, perhaps the cell contains numeric 0 but you have display zero values turned off so the cell might appear blank/empty. ???? -- Biff Microsoft Excel MVP "Kay" wrote in message ... Hi all, I have a peculiar problem with a function. I am using Excel 2007. I am working with about 57,000 records. I must match the rate of an audio call based on two conditions. I am using the following formula against a list that is sorted in ascending order: =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) I can find a match for all but 600 or so records. There is a match, without a doubt for the other 600. I have made sure that the text the lookup is based on is clean...no trailing or leading spaces. I have even cleared the cells and retyped the data just as it is in the lookup table. No matter how many times, I write it or how I modify the function, those records return 0. I have also updated Excel, ran diagnostics, copied the data to a new workbook, opened and repaired...I just do not know what else to try. Of course, the report is due Friday morning. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Function Help
Check out whether the named range 'Entity' refers to $E$34:$E$98...
=INDEX('Rate Table'!$E$34:$H$98, MATCH('Veri vs Interc Clean Data 2010 '!G597,'Rate Table'!$E$34:$E$98,0), MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) -- Jacob "Kay" wrote: Hi all, I have a peculiar problem with a function. I am using Excel 2007. I am working with about 57,000 records. I must match the rate of an audio call based on two conditions. I am using the following formula against a list that is sorted in ascending order: =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) I can find a match for all but 600 or so records. There is a match, without a doubt for the other 600. I have made sure that the text the lookup is based on is clean...no trailing or leading spaces. I have even cleared the cells and retyped the data just as it is in the lookup table. No matter how many times, I write it or how I modify the function, those records return 0. I have also updated Excel, ran diagnostics, copied the data to a new workbook, opened and repaired...I just do not know what else to try. Of course, the report is due Friday morning. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Function Help
You may also use VLOOKUP() as below
=VLOOKUP('Veri vs Interc Clean Data 2010 '!G597, 'Rate Table'!$E$34:$H$98, MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0),0) -- Jacob "Jacob Skaria" wrote: Check out whether the named range 'Entity' refers to $E$34:$E$98... =INDEX('Rate Table'!$E$34:$H$98, MATCH('Veri vs Interc Clean Data 2010 '!G597,'Rate Table'!$E$34:$E$98,0), MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) -- Jacob "Kay" wrote: Hi all, I have a peculiar problem with a function. I am using Excel 2007. I am working with about 57,000 records. I must match the rate of an audio call based on two conditions. I am using the following formula against a list that is sorted in ascending order: =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) I can find a match for all but 600 or so records. There is a match, without a doubt for the other 600. I have made sure that the text the lookup is based on is clean...no trailing or leading spaces. I have even cleared the cells and retyped the data just as it is in the lookup table. No matter how many times, I write it or how I modify the function, those records return 0. I have also updated Excel, ran diagnostics, copied the data to a new workbook, opened and repaired...I just do not know what else to try. Of course, the report is due Friday morning. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Function Help
Thanks to all who have replied so far.
I agree that it is finding something with the 0. It has found something for all other 51,000. The value that it should find is $0.068. I have checked all formatting , both the formula cell and the rate table cells. I checked options for any odd behaviour there. I have not turned on the option to have zero values turned off in either case, the formula cell or the rate table. The entity range name does refer to the correct area and has worked for the rest of the formulas correctly. I tried the vlookup, but it returns an #NA. Any other ideas. "T. Valko" wrote: No matter how many times, I write it or how I modify the function, those records return 0. =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) If your formula is returning 0 that means the MATCH functions *have found* matches of the lookup values. If they weren't finding matches then one or the other (or possibly both) MATCH function would cause a #N/A error. A 0 could indicate that the cell is empty or it actually does contain a numeric 0. Or, perhaps the cell contains numeric 0 but you have display zero values turned off so the cell might appear blank/empty. ???? -- Biff Microsoft Excel MVP "Kay" wrote in message ... Hi all, I have a peculiar problem with a function. I am using Excel 2007. I am working with about 57,000 records. I must match the rate of an audio call based on two conditions. I am using the following formula against a list that is sorted in ascending order: =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) I can find a match for all but 600 or so records. There is a match, without a doubt for the other 600. I have made sure that the text the lookup is based on is clean...no trailing or leading spaces. I have even cleared the cells and retyped the data just as it is in the lookup table. No matter how many times, I write it or how I modify the function, those records return 0. I have also updated Excel, ran diagnostics, copied the data to a new workbook, opened and repaired...I just do not know what else to try. Of course, the report is due Friday morning. Any help would be appreciated. . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and Match Function Help
Your lookup table isn't that big.
I'd suggest you break the formula down to its individual components and then inspect the cell that the formula refers to. For example: For the relative row of the lookup table: =MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0) For the relative column of the lookup table: =MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0) Then trace that intersection and see what the cell value is. -- Biff Microsoft Excel MVP "Kay" wrote in message ... Thanks to all who have replied so far. I agree that it is finding something with the 0. It has found something for all other 51,000. The value that it should find is $0.068. I have checked all formatting , both the formula cell and the rate table cells. I checked options for any odd behaviour there. I have not turned on the option to have zero values turned off in either case, the formula cell or the rate table. The entity range name does refer to the correct area and has worked for the rest of the formulas correctly. I tried the vlookup, but it returns an #NA. Any other ideas. "T. Valko" wrote: No matter how many times, I write it or how I modify the function, those records return 0. =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) If your formula is returning 0 that means the MATCH functions *have found* matches of the lookup values. If they weren't finding matches then one or the other (or possibly both) MATCH function would cause a #N/A error. A 0 could indicate that the cell is empty or it actually does contain a numeric 0. Or, perhaps the cell contains numeric 0 but you have display zero values turned off so the cell might appear blank/empty. ???? -- Biff Microsoft Excel MVP "Kay" wrote in message ... Hi all, I have a peculiar problem with a function. I am using Excel 2007. I am working with about 57,000 records. I must match the rate of an audio call based on two conditions. I am using the following formula against a list that is sorted in ascending order: =INDEX('Rate Table'!$E$34:$H$98,MATCH('Veri vs Interc Clean Data 2010 '!G597,Entity,0),MATCH('Veri vs Interc Clean Data 2010 '!E597,'Rate Table'!$E$33:$H$33,0)) I can find a match for all but 600 or so records. There is a match, without a doubt for the other 600. I have made sure that the text the lookup is based on is clean...no trailing or leading spaces. I have even cleared the cells and retyped the data just as it is in the lookup table. No matter how many times, I write it or how I modify the function, those records return 0. I have also updated Excel, ran diagnostics, copied the data to a new workbook, opened and repaired...I just do not know what else to try. Of course, the report is due Friday morning. Any help would be appreciated. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX w. MATCH function | Excel Worksheet Functions | |||
Help with Index / Match function | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
index match function help me | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |