Excel - INDEX MATCH Function - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.