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.

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.

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.

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.

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.


.

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.


.