Excel - Index and Match Function Help

Asked By Kay

28-Jan-10 10:10 PM

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.

28-Jan-10 10:26 PM

'!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 were not 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

29-Jan-10 12:20 AM

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

29-Jan-10 12:27 AM

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

29-Jan-10 08:17 AM

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.

29-Jan-10 06:23 PM

Your lookup table is not 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