Index/Match/Match....Stops without completing the match

**irsles** · 10-14-2012, 08:43 PM

Help Please!
I have an Index/Match/Match in Excel 2010. It is a large table which I would like to add to in the future.

My formula reads as follows:
=INDEX(C3:SD411, MATCH(B413,B3:B411,0), MATCH(B414,C2:SD2,0))

The column match is working fine (B3:B411), however the row match (C2:SD2) stops at column DN, that is to say that the last column it will look to return a value from is DN. I have been looking for hours for the problem online and I am not finding any postings of this problem, not to mention a solution.

Can someone help me resolve this? Thanks
L

**FDibbins** · 10-14-2012, 08:59 PM

could you upload a (small) sample of your workbook for us to look at? i have never worked with a sheet with 500 columns before????

**irsles** · 10-14-2012, 09:24 PM

i would be willing to upload the whole sheet....seems that would be best if want to determine why it is stopping where it is. could you please advise me how to upload the spreadsheet. thanks

**vlady** · 10-14-2012, 09:26 PM

Hello Irsles.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**irsles** · 10-14-2012, 09:36 PM

i have attempted to upload, the file is too large. i attempted to trim it, but still too large. is there a way i can email direct to you?

**irsles** · 10-14-2012, 10:05 PM

OK.....I was able to compress the file and shrink to a size I could upload. Please feel free to review and offer any suggestions available. I am wondering if it is an inherent quirk in excel. Thanks for your patience!

L

**FDibbins** · 10-14-2012, 10:16 PM

ok 1 reason is because in the C2:SD2 range, the value is 2.25999999999999% you are trying to find 2.26

DK2 = 2.21999999999999%
DP2 = 2.26999999999999%
in fact you have 376 entries that are in that "format". to find them, in row 1, use this, copied across =LEN(C2). check for any values over about 6

correct those and your problem should be solved. i tested it wqith a rate of 6.04999999999997% (the last value in SD) and it returned 12.1