The same INDEX/MATCH formual is giving different results, and I can't work out why

Red over White

Board Regular
Joined
Jul 16, 2011
Messages
93
I am trying to develop a new Excel file by simplifying an old one, because too many add-ons on the old one have made it too difficult to make constructive improvements. I have been pulling across worksheets individually, and the tried to make everything consistent. I have come across an issue with the formula on row 23. The formulas are effectively the same, the only difference is the column references. The calculation in row 23 should match the dates on rows 14 and 46, and then make a calculation based on the figures in rows 3 and either rows 14 to 17, depending on the date in row 46. With the exception of column K, the formula is working as intended, and is looking at the right figure in 18 to 21. The K column is using the wrong figure, it should be using 27.96 rather than 42.47. I can't work out why it is doing this and was wondering if there is another way of getting the desired result.

Thanks in advance.

Nick



INDEXMATCH.xlsx
BCDEFGHIJKLMN
320420453821301309171830490926395821643
4554.004,849.29184.631,775.80759.342,889.00319.56562.861,921.82830.75856.80304.98
516/06/9730/11/1706/11/1702/10/1719/10/1702/10/1713/08/1218/02/9925/06/1501/12/2010/10/1729/11/17
626.0012.5012.5010.5012.509.9512.5012.5010.5012.50
749.4649.6811.5449.7024.7013.268.8426.5425.05
88,187.10
901/05/20
1027/06/17
11443
12
1314/05/2130/03/2001/04/2131/01/2003/02/2128/05/2122/06/2024/02/2108/04/2120/03/2028/02/2126/05/20
1410/12/2008/08/1908/08/1919/12/1930/05/1903/09/2010/10/1920/06/1925/02/2130/05/1919/09/19
1526/11/20
16
1708/04/2128/02/2026/02/2127/06/1917/12/2013/05/2107/05/2016/01/2113/08/2027/02/2002/01/2116/04/20
187.0090.003.0011.308.3053.001.5013.1027.962.451.95
1925.00
20
2114.00190.001.0031.5021.6054.003.5026.9042.4743.903.753.54
221.00000.77051.00001.00001.00001.00001.00001.00001.00001.00000.75011.0000
2328.560.0053.820.00282.7492.340.00119.1739.070.0016.370.00
24
25CDEFGHIJKLMN
26AEIMQUYACAGAKAOAS
27BFJNRVZADAHALAPAT
28CGKOSWAAAEAIAMAQAU
29DHLPTXABAFAJANARAV
30 C
31SSSSSS
32120            
33
342Z
35
362042045,3821301,309171830443926395821,643
371,130.169,968.029,998.702,332.589,999.964,977.392,675.582,493.481,789.415,347.554,997.065,048.38
38
39
40
41
42
43
4414/05/2100/01/0001/04/2131/01/2003/02/2128/05/2122/06/2024/02/2108/04/2120/03/2028/02/2126/05/20
4508/04/2100/01/0026/02/2119/12/1917/12/2013/05/2107/05/2016/01/2125/02/2127/02/2002/01/2116/04/20
4612008/04/2100/01/0026/02/2100/01/0017/12/2013/05/2100/01/0016/01/2125/02/2100/01/0002/01/2100/01/00
47
Stock
Cell Formulas
RangeFormula
C23:N23C23=IF(OR(C34="Z",C46=0,C5>C46),0,INDEX(C18:C21,MATCH(C46,C14:C17),FALSE)*C36*C22/100)
C25:N25C25='/Users/nickdoyle/Nick''s folder/Filthy lucre/[Finances 2021.xlsm]Base H'!C1
C26:N29C26='/Users/nickdoyle/Nick''s folder/Filthy lucre/[Finances 2021.xlsm]Base H'!A13
C32:N32C32=IF(OR(C10<=NOW()-INDIRECT("B"&ROW()),C10=0),"","R")
D34D34=IF(D8<>0,"Z",INDIRECT("Stock22!"&D27&$B34))
C36:N36C36=IF(C11>0,C11,C3)
C37:N37C37=SUM(C36*C4)/100+SUM(C6:C7)
C44:N44C44=IF(C$34="Z",0,C13)
C45:N45C45=IF(C$34="Z",0,MAX(C14:C17))
C46:N46C46=IF(MAX(C14:C17)>NOW()-$B46,MAX(C14:C17),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
22:22Cell Value=1textNO
BF47:XFD47,A47:AW47Cell Value=0textNO
C41:AW41Cell Valuebetween 1 and 10000textNO
1:1048576Cell Value=0textNO
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You have a bracket in the wrong place, try
Excel Formula:
=IF(OR(K34="Z",K46=0,K5>K46),0,INDEX(K18:K21,MATCH(K46,K14:K17,FALSE))*K36*K22/100)
 
Solution

Red over White

Board Regular
Joined
Jul 16, 2011
Messages
93
Thanks - that has worked. It has taught me to take a break, as I was nearly on it for ten hours.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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
Top