Match returning N/a-

JohnH58

New Member
Joined
Nov 6, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I have used the match() function many times. I am using a simple match function but why it is returning an error has me puzzled. I have added an image of the spreadsheet here.
1. Column L is formatted as a number with 2 decimals.
2. My table array is in Column AI Rows 1352 - 1366. This is also formatted as a number with 2 decimal places.
3. Above my table array in rows 1341-1346 is the formula returning the N/a in all but 1 cell.
4. The formula I am using is =MATCH(L1346,$AI$1352:$AI$1366,0). For clarity L1346 is moving with each cell I copy.
5. Each of the values I am looking for exist so I am using the (0-Exact Match) option in the match.
6. I have tested each of the cells for blanks etc using the Len() function and all appears fine.

This should be a relatively simple formula, I am using it in a more complex Index,Match function but have only shown the match problem here as that is the element of the formula that is causing problems.
I would appreacte any steer I can get on this one, it has me stumped!

JohnH
 

Attachments

  • Capture.JPG
    Capture.JPG
    133.3 KB · Views: 13

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Hi, please try prepare a data example using add-in specifically for this and it can be found here XL2BB
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,859
Office Version
  1. 365
Platform
  1. Windows
Try expanding the decimals out past 2 places to see if the numbers are actual equal. Formatting a cell to 2 decimal places doesn't change the actual number in the cell, only what you see. If that's the issue use the ROUND function set to 2 decimal places.
 

JohnH58

New Member
Joined
Nov 6, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi, please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Forster Financials.xlsm
FIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
13413/10/20201575216.560.00247.011111.43463.57771%0.00%16%$ 22523%20206-7 Days$ 216.561BRX7+#N/A
134210/10/20202310317.630.00247.011745.37564.64776%0.00%11%$ 33023%20206-7 Days$ 317.631BRX7+#N/A
134314/10/20201120154.000.00148.39817.61302.39473%0.00%13%$ 28013%20203-5 Days$ 154.002BRX2#N/A
134426/10/2020900123.750.00182.14594.11305.89366%0.00%20%$ 30010%20203-5 Days$ 123.753BRX2#N/A
134527/10/202017023.380.0084.9661.67108.34136%0.00%50%$ 1703%20201 Day$ 23.38 #N/A
134630/10/202054074.258.10112.53345.12194.88364%1.50%21%$ 18010%20203-5 Days$ 82.351BRX3-63
134731/10/2020178.550661725126-462.50462.50MTH SUMMARYMTH SUMMARYMTH SUMMARYMTH SUMMARY  2020#N/A$ - 
1348
1349
1350
1351
1352184.96
13532104.59
13543112.53
13554128.79
13565136.73
13576148.39
13587151.20
13598158.34
13609162.25
136110182.14
136211213.68
136312227.53
136413247.01
136514322.84
136615381.70
Data
Cell Formulas
RangeFormula
W1341:W1347W1341=I1341-SUM(J1341:V1341)
X1341:X1347X1341=SUM(J1341:V1341)
Y1341:Y1347Y1341=IF(F1341-E1341=0,"MTH SUMMARY",F1341-E1341)
Z1341:Z1347Z1341=IF(ISERROR(SUM(J1341:N1341)/I1341),"MTH SUMMARY",1-SUM(J1341:N1341)/I1341)
AA1341:AA1347AA1341=IF(ISERROR(K1341/I1341),"MTH SUMMARY",K1341/I1341)
AB1341:AB1347AB1341=IF(ISERROR(L1341/I1341),"MTH SUMMARY",L1341/I1341)
AC1341:AC1347AC1341=IF(ISERROR(I1341/Y1341),"",I1341/Y1341)
AD1341:AD1347AD1341=IF(ISERROR(Y1341/30),"",Y1341/30)
AE1341:AE1347AE1341=TEXT(E1341,"yyyy")
AF1341:AF1347AF1341=VLOOKUP(Y1341,Tables!$L$20:$N$25,3)
AG1341:AG1347AG1341=J1341+K1341+P1341
AH1341:AH1346AH1341=IF(ISERROR(VLOOKUP(L1341,Tables!$U$55:$V$69,2)),"",VLOOKUP(L1341,Tables!$U$55:$V$69,2))
AI1341:AI1346AI1341=MATCH(L1341,$AI$1352:$AI$1366,0)
AH1347AH1347=IF(ISERROR(VLOOKUP(L1347,Tables!$R$6:$S$20,2)),"",VLOOKUP(L1347,Tables!$R$6:$S$20,2))
 

JohnH58

New Member
Joined
Nov 6, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try expanding the decimals out past 2 places to see if the numbers are actual equal. Formatting a cell to 2 decimal places doesn't change the actual number in the cell, only what you see. If that's the issue use the ROUND function set to 2 decimal places.
Great thought, but no there is no decimals beyond 2. I expanded and checked.
 

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Hi, it is the output that you looking for?

Book1
FGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1340
13413-Oct-20201575216.56250247.011111.4275463.5725441070.70566825400.1568317460.0357086181470.2333331900#N/A216.5625 13
134210-Oct-20202310317.6250247.011745.365564.635441140.75556926400.1069307360.0523643291470.4666671900#N/A317.625 13
134314-Oct-202011201540148.39817.61302.39441180.73000892900.1324910710.0253864641470.61900#N/A154 6
134426-Oct-2020900123.750182.14594.11305.89441300.66012222200.2023777780.0203942914711900#N/A123.75 10
134527-Oct-202017023.375084.9661.665108.335441310.36273529400.4997647060.0038521671471.0333331900#N/A23.375 1
134630-Oct-202054074.258.1112.53345.12194.88441340.6391111110.0150.2083888890.0122354651471.1333331900#N/A82.35 3
134731-Oct-2020322.84178.550661725126-785.34785.3444135MTH SUMMARYMTH SUMMARYMTH SUMMARY01471.1666671900#N/A0 14
1348
1349
1350
1351
1352184.96
13532104.59
13543112.53
13554128.79
13565136.73
13576148.39
13587151.2
13598158.34
13609162.25
136110182.14
136211213.68
136312227.53
136413247.01
136514322.84
136615381.7
Sheet3
Cell Formulas
RangeFormula
W1341:W1347W1341=I1341-SUM(J1341:V1341)
X1341:X1347X1341=SUM(J1341:V1341)
Y1341:Y1347Y1341=IF(F1341-E1341=0,"MTH SUMMARY",F1341-E1341)
Z1341:Z1347Z1341=IF(ISERROR(SUM(J1341:N1341)/I1341),"MTH SUMMARY",1-SUM(J1341:N1341)/I1341)
AA1341:AA1347AA1341=IF(ISERROR(K1341/I1341),"MTH SUMMARY",K1341/I1341)
AB1341:AB1347AB1341=IF(ISERROR(L1341/I1341),"MTH SUMMARY",L1341/I1341)
AC1341:AC1347AC1341=IF(ISERROR(I1341/Y1341),"",I1341/Y1341)
AD1341:AD1347AD1341=IF(ISERROR(Y1341/30),"",Y1341/30)
AE1341:AE1347AE1341=TEXT(E1341,"yyyy")
AF1341:AF1347AF1341=VLOOKUP(Y1341,Tables!$L$20:$N$25,3)
AG1341:AG1347AG1341=J1341+K1341+P1341
AH1341:AH1346AH1341=IF(ISERROR(VLOOKUP(L1341,Tables!$U$55:$V$69,2)),"",VLOOKUP(L1341,Tables!$U$55:$V$69,2))
AI1341:AI1347AI1341=MATCH(L1341,$AI$1352:$AI$1366,0)
AH1347AH1347=IF(ISERROR(VLOOKUP(L1347,Tables!$R$6:$S$20,2)),"",VLOOKUP(L1347,Tables!$R$6:$S$20,2))
 

JohnH58

New Member
Joined
Nov 6, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes it is. However, what I don't understand is the formula is no different to mine that is returning the error result. See screenshot attached displaying both formulas. I am still stumped?
 

Attachments

  • Capture.JPG
    Capture.JPG
    241.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,646
Office Version
  1. 365
Platform
  1. Windows
If you put this into a blank cell what does it return
Excel Formula:
=(L1344-182.14)*10^15
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,646
Office Version
  1. 365
Platform
  1. Windows
Ok, what if you use
Excel Formula:
=L1344=AI1361
 

Forum statistics

Threads
1,143,677
Messages
5,720,260
Members
422,273
Latest member
linds75

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