# Index Match Problem

1. ## Index Match Problem

I've recently learned how to use the INDEX MATCH function but have a problem. I've put together a lookup using INDEX MATCH that works fine in most workbooks but won't work in a workbook provided by a Local Authority unless I copy and paste the text from the workbook where I keep the original INDEX MATCH example into the Local Authority workbook.

I've tried using format painter to try and match the formatting of the 2 workbooks but to no avail....HELP!!

2. ## Re: Index Match Problem

Hello,

Can you attach the sample file in which your formula does not work? It will be easier for other to help you.

3. ## Re: Index Match Problem

I'll do it tomorrow Lemice as I have to hit the sack for work in the morning!

4. ## Re: Index Match Problem

Just FYI, formatting just changes how data looks, it does nothing to change the actual data itself.

Chech for leading/trailing spaces in the data you are searching. If you can find 2 sets of data that should match - but dont, use =exact(cell1,cell2) to see if they are in fact the same

5. ## Re: Index Match Problem

Problem Worksheet Index Match.xlsx

FDibbins

I've tried removing leading spaces with a macro I have but to no avail; also used the =exact function to compare cells that were recognised by the Index Match and those that were not...got a "TRUE" answer.

Lemice

I've created an example of the problem I'm having by pasting in cells from the problem worksheet into the attached spreadsheet. As you can see, the first row works because I've cut and pasted data from the original data table into this row. The rows showing #N/A are from the problem worksheet.

6. ## Re: Index Match Problem

YOu have trailing spaces in column Q
In row4 (the one that works) there IS a trailing space on D4
In row5 and beyond, there is NOT a trailing space in D

Try this in F4
=INDEX(\$N\$4:\$R\$63,MATCH(TRIM(A4&B4&C4&D4),TRIM(\$N\$4:\$N\$63&\$O\$4:\$O\$63&\$P\$4:\$P\$63&\$Q\$4:\$Q\$63),0),5)

7. ## Re: Index Match Problem

Cheers Jonmo1; I've identified a problem in column D...if I change this one cell to the corresponding cell that does work the formula works. I'm going to try your formula and if it works I'm going to source a macro to remove trailing spaces.

8. ## Re: Index Match Problem

Try using Data - Text To Columns - Deliminated - Space - Finish.
On both columns from Row4 down

9. ## Re: Index Match Problem

Yes!!!

``Please Login or Register  to view this content.``
Next
Application.Calculation = xlAutomatic
End Sub[/CODE]Thanks guys, particularly Jonmo1 for taking a look at this for me so quickly; really appreciated.

11. ## Re: Still Have A Problem!!

Format Problem.xlsx

I'm still having problems with the worksheet; I've run the macro to remove the trailing and leading spaces but the INDEX MATCH function is again not working in the cells highlighted in red.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1