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.
|