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.
