Index - Match Issue

ProWoods

New Member
Joined
Apr 3, 2020
Messages
11
Office Version
  1. 365
  2. 2013
  3. 2007
Platform
  1. Windows
Good morning,

I have a Spreadsheet that has 557 Rows, Columns A-I, the Index - Match works fine, but I added a Row, Updated my Index and Match, but it does not find the Data in Row 558. It is Not a Table, has No Define Name Range, Calculations are set to Automatic. No matter what I have tried, including re-typing the formula in a different cell, it will not look past Row 557.

The Formula: {=INDEX(C2:C558, MATCH(1, (K2=E2:E558)*(L2=F2:F558)*(M2=G2:G558),0),1)}

Yes, I did the Ctrl+Shift+Enter.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Maybe you need to set the references as absolute?:
Excel Formula:
=INDEX($C$2:$C$558, MATCH(1, ($K$2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),0),1)

also since you have 365, why not use XLOOKUP (this is whinged, but, I hope you understand where I'm going if I have an argument placed incorrectly.)
Excel Formula:
=XLOOKUP(1,(K2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),$C$2:$C$558,1,0)
 
Upvote 0
Maybe you need to set the references as absolute?:
Excel Formula:
=INDEX($C$2:$C$558, MATCH(1, ($K$2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),0),1)

also since you have 365, why not use XLOOKUP (this is whinged, but, I hope you understand where I'm going if I have an argument placed incorrectly.)
Excel Formula:
=XLOOKUP(1,(K2=$E$2:$E$558)*(L2=$F$2:$F$558)*(M2=$G$2:$G$558),$C$2:$C$558,1,0)
I have just tried both of you suggestions, in the Index Match, no change, Using XLookUp, gives me a #NAME?. Using Absolute in both situation makes no difference.
 
Upvote 0
I have just tried both of you suggestions, in the Index Match, no change, Using XLookUp, gives me a #NAME?. Using Absolute in both situation makes no difference.
i suggest posting a small sample of your data using the xl2bb add in or share a sanitized version of your workbook. The formulas below work. You could close the workbook and see if it may still cause the errors.
Are you sure that the criteria you're entering matches an entire ROW for all three columns?
Book1
ABCDEFGHIJKLM
1
2INDEX MATCH481CTKFIOQFKZRXPEAKPS
3XLOOKUP482TKHEQJUZB
43JCDTFBBAG
54BLLJRUTGR
65IDMDTQDMN
76JNBEXGDPD
87XHXSQVGTW
98CZKAVMDKZ
109WGGZGSPQB
1110HNHFTXEHQ
1211SBDVFNURE
1312XILFFHFRW
1413ZWFRCNMFX
1514TQCBSNYAT
1615QAUHHBDEY
1716FKRXNCOGN
1817FALWURFQD
1918EMJFGSSXD
2019VDTFJKVLC
2120SDQVSEUCA
2221FLKLWYBXK
2322IMPMYAUQL
2423FTAYMUOQS
2524DAUYOBGNE
2625WQHFGWNJK
2726OJJLXGQKM
2827CXONORWLR
2928PZEQDTFJR
3029FRUHKOSBU
3130MFLWNOXQF
3231OAITNAYWZ
3332FAEFZXICA
3433SXLAVTKRZ
3534FCGUCAYJK
3635KEJWLQFNG
3736IEDZVFBVP
3837XJNYTPIPK
3938KWNCPNOET
4039WTSLOZCAJ
4140JANDGBICD
4241GREJPLZUC
4342JBGEUXECN
4443BPWONEZUR
4544SNMAXHRWI
4645SEYKGCEGZ
4746OFUKWOXRH
4847DKWXOHLQI
4948ZRXPEAKPS
Sheet1
Cell Formulas
RangeFormula
B2B2=INDEX(C2:C49, MATCH(1, (K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),0),1)
B3B3=XLOOKUP(1,(K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),$C$2:$C$49,"not found",0)
 
Last edited:
Upvote 0
I have just tried both of you suggestions, in the Index Match, no change, Using XLookUp, gives me a #NAME?. Using Absolute in both situation makes no difference.
I am running Pro 2016
 
Upvote 0
i suggest posting a small sample of your data using the xl2bb add in or share a sanitized version of your workbook. The formulas below work. You could close the workbook and see if it may still cause the errors.

Are you sure that the criteria you're entering matches an entire ROW for all three columns?
Book1
ABCDEFGHIJKLM
1
2INDEX MATCH481CTKFIOQFKZRXPEAKPS
3XLOOKUP482TKHEQJUZB
43JCDTFBBAG
54BLLJRUTGR
65IDMDTQDMN
76JNBEXGDPD
87XHXSQVGTW
98CZKAVMDKZ
109WGGZGSPQB
1110HNHFTXEHQ
1211SBDVFNURE
1312XILFFHFRW
1413ZWFRCNMFX
1514TQCBSNYAT
1615QAUHHBDEY
1716FKRXNCOGN
1817FALWURFQD
1918EMJFGSSXD
2019VDTFJKVLC
2120SDQVSEUCA
2221FLKLWYBXK
2322IMPMYAUQL
2423FTAYMUOQS
2524DAUYOBGNE
2625WQHFGWNJK
2726OJJLXGQKM
2827CXONORWLR
2928PZEQDTFJR
3029FRUHKOSBU
3130MFLWNOXQF
3231OAITNAYWZ
3332FAEFZXICA
3433SXLAVTKRZ
3534FCGUCAYJK
3635KEJWLQFNG
3736IEDZVFBVP
3837XJNYTPIPK
3938KWNCPNOET
4039WTSLOZCAJ
4140JANDGBICD
4241GREJPLZUC
4342JBGEUXECN
4443BPWONEZUR
4544SNMAXHRWI
4645SEYKGCEGZ
4746OFUKWOXRH
4847DKWXOHLQI
4948ZRXPEAKPS
Sheet1
Cell Formulas
RangeFormula
B2B2=INDEX(C2:C49, MATCH(1, (K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),0),1)
B3B3=XLOOKUP(1,(K2=$E$2:$E$49)*(L2=$F$2:$F$49)*(M2=$G$2:$G$49),$C$2:$C$49,"not found",0)
Yes, the Rows Index and Match, I can Upload the complete Workbook, it is just a Color Chart for Access.
 
Upvote 0
Yes, the Rows Index and Match, I can Upload the complete Workbook, it is just a Color Chart for Access.
It will not do any good to use xl2bb, it cannot Upload a Complete Sheet Range. I have not issues from Row 1 to Row 557, it is just below that range that seems to have an issue.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top