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: 16

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi, please try prepare a data example using add-in specifically for this and it can be found here XL2BB
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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
Upvote 0
If you put this into a blank cell what does it return
Excel Formula:
=(L1344-182.14)*10^15
 
Upvote 0
Ok, what if you use
Excel Formula:
=L1344=AI1361
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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