**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have an index match match formula that isnt working for part of the data. Would you be able to assist with this?

Solved by E. U. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
22/05/2018 - 06:26

Hi…Welcome to Got It Pro

User
22/05/2018 - 06:26

would the formula be helpful?

Excelchat Expert
22/05/2018 - 06:26

Sure, I can help you with the Index-Match

User
22/05/2018 - 06:26

=IFERROR(INDEX('SS18'!$A$1:$CL$389,MATCH('SSWORKING BRAND FRST - CAT'!$J83,'SS18'!$A$1:$A$389,0),MATCH('SSWORKING BRAND FRST - CAT'!EF$40,'SS18'!$A$1:$CL$1,0))+INDEX('SS18'!$A$1:$CL$389,MATCH('SSWORKING BRAND FRST - CAT'!$J83,'SS18'!$A$1:$A$389,0),MATCH('SSWORKING BRAND FRST - CAT'!EF$39,'SS18'!$A$1:$CL$1,0))+INDEX('PF18'!$A$1:$CL$389,MATCH('SSWORKING BRAND FRST - CAT'!$J83,'PF18'!$A$1:$A$389,0),MATCH('SSWORKING BRAND FRST - CAT'!EF$40,'PF18'!$A$1:$CL$1,0))+INDEX('PF18'!$A$1:$CL$389,MATCH('SSWORKING BRAND FRST - CAT'!$J83,'PF18'!$A$1:$A$389,0),MATCH('SSWORKING BRAND FRST - CAT'!EF$39,'PF18'!$A$1:$CL$1,0)),0)

Excelchat Expert
22/05/2018 - 06:27

Could you share the file?

User
22/05/2018 - 06:27

i cant.. unfortunately due to confidentiality

User
22/05/2018 - 06:28

I think it has to do with the second half of the formula

Excelchat Expert
22/05/2018 - 06:28

ok...is it possible to illustrate the sample data in this blank sheet?

Excelchat Expert
22/05/2018 - 06:28

the formula is a sum of several index-match function

User
22/05/2018 - 06:29

yes... its a big and complex file, so I dont think id be able to emmulate the data

Excelchat Expert
22/05/2018 - 06:29

so to troubleshoot, you can actually check if which part is not working by using on individual section of index match

User
22/05/2018 - 06:30

it's the second half I believe... I think it might not be locked properly, because its picking up the upper part of the document but not the lower

Excelchat Expert
22/05/2018 - 06:30

I can explain to you the formula if you can understand how it is working

Excelchat Expert
22/05/2018 - 06:31

First use, only this section =INDEX('SS18'!$A$1:$CL$389,MATCH('SSWORKING BRAND FRST - CAT'!$J83,'SS18'!$A$1:$A$389,0),MATCH('SSWORKING BRAND FRST - CAT'!EF$40,'SS18'!$A$1:$CL$1,0))

Excelchat Expert
22/05/2018 - 06:31

and see if you get the result

User
22/05/2018 - 06:31

i do fpr both

Excelchat Expert
22/05/2018 - 06:32

so can you tell me which part is not working

User
22/05/2018 - 06:32

the data for all values isnt in both sets, so some are n/A

User
22/05/2018 - 06:32

the pf18 reference catures only half on the data. the matches at the bottom of the pf18 sheet are cut off

Excelchat Expert
22/05/2018 - 06:33

Its really difficult to troubleshoot without actually seeing the issue

Excelchat Expert
22/05/2018 - 06:38

With this formula, you are only taking table until row number 389

Excelchat Expert
22/05/2018 - 06:38

You may try extending it to the last row so that it captures the bottom data

Excelchat Expert
22/05/2018 - 06:39

So in the last section of the formula you see INDEX('PF18'!$A$1:$CL$389

User
22/05/2018 - 06:39

data goes to 267

Excelchat Expert
22/05/2018 - 06:39

Then there is no problem as it is looking till row 389

User
22/05/2018 - 06:40

is it locked properly, so as I drag it across 1000+ rows it will still reference the same area

Excelchat Expert
22/05/2018 - 06:40

yes

User
22/05/2018 - 06:40

I think that is the issue

Excelchat Expert
22/05/2018 - 06:41

It is currently locked till row 389

Excelchat Expert
22/05/2018 - 06:41

of the tables that you are indexing to fetch the results

Excelchat Expert
22/05/2018 - 06:44

Your formula is perfectly alright syntax wise, so its difficult without the data to troubleshoot

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*