Index Match Date N/A

jcmac

New Member
Joined
Nov 7, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
I am trying to use index match but cannot get it to work, it works when I manually enter the time and date into the cells next to the yellow box but when using the formula ref i get n/a error.

Indext Match Formula

=INDEX(B37:Y67,MATCH(E4,A37:A67,0),MATCH(F4,B36:Y36,0))
 

Attachments

  • Untitled.png
    Untitled.png
    200.3 KB · Views: 14

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
688
Office Version
  1. 2016
Try this:

=INDEX(B37:Y67,MATCH(E4,A37:A67,0),HOUR(F4)+1)
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
If Phuoc's suggestion doesn't work, then themost likely issue is that the formulas used are not returning a date value or time value but text. Or the date part is not including the year and defaulting to a different year.
Start with supplying us with an XL2BB so we can actually see what you have in the cells.
 

jcmac

New Member
Joined
Nov 7, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
SailingDrafts (2).xlsx
ABCDEFGHIJKLMNO
1HW -2 Hours Tide
2DTDTDate x1Time A (x1)A height (y1)Time B (x3)B height (y3)Time C (x2)HeightMax DraftRuling Depth18.50m
330/11 23:2030/11 21:2030/1121:0022:0021:200.0016.40UKC Min2.10m
401/12 11:4401/12 09:4401/129:0010:009:440.0016.40
502/12 00:1201/12 22:1201/1222:0023:0022:120.0016.40
602/12 12:4302/12 10:4302/1210:0011:0010:430.0016.40
703/12 01:0202/12 23:0202/1223:000:0023:020.0016.40
803/12 13:2903/12 11:2903/1211:0012:0011:290.0016.40
Working Sheet
Cell Formulas
RangeFormula
E3:E8E3=F3
F3:F8F3=FLOOR(C3,TIME(1,0,0))
J3:J8J3=C3
K3:K8K3=SUM(((J3-F3)*(I3-G3))/(H3-F3))+G3
L3:L8L3=SUM(18.5+K3-2.1)
C3:C8C3=SUM(A3-(2/24))
H3:H8H3=CEILING(C3,TIME(1,0,0))


SailingDrafts (2).xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Dec-21
200:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
315.044.473.462.361.631.331.281.652.473.534.525.155.345.044.243.142.171.691.591.852.603.644.595.20
425.475.324.593.352.051.281.051.071.562.633.894.885.475.625.174.132.781.711.321.301.652.643.934.93
535.515.765.524.593.121.701.000.860.951.632.974.325.225.745.805.173.862.321.301.041.071.552.804.26
645.215.745.985.614.462.791.400.860.790.961.893.444.725.475.925.875.003.471.871.010.860.931.583.06
754.525.365.886.095.564.202.481.230.850.821.132.313.914.995.606.005.794.713.041.530.860.780.901.73
863.314.645.385.926.075.373.892.231.210.960.981.462.804.285.125.645.975.594.362.681.330.850.811.00
971.953.474.615.305.865.915.093.602.111.321.171.251.893.244.495.125.585.825.324.022.421.290.960.94
Heights
Cell Formulas
RangeFormula
A4:A9A4=SUM(A3+1)
 

mackc557

Board Regular
Joined
Nov 3, 2021
Messages
117
Office Version
  1. 2019
Platform
  1. Windows
With your original posted picture there shouldn't be any reason it's not working. However with your sample, the numbers under the Dec-21 are just integers, not dates.
 

jcmac

New Member
Joined
Nov 7, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
I have been looking more and narrowed the issue down to the date field in column E. If the date is extracted from the column H it doesn't work, but if entered manually good.

SailingDrafts (2).xlsx
ABCDEFGHIJKLMNO
1HW -2 Hours Tide
2DTDTDate x1Time A (x1)A height (y1)Time B (x3)B height (y3)Time C (x2)HeightMax DraftRuling Depth18.50m
330/11 23:2030/11 21:2030/1121:0022:0021:200.0016.40UKC Min2.10m
401/12 11:4401/12 09:4401/129:003.5310:009:440.9417.34
502/12 00:1201/12 22:1201/1222:00#N/A23:0022:12#N/A#N/A
602/12 12:4302/12 10:4302/1210:0011:0010:430.0016.40
Working Sheet
Cell Formulas
RangeFormula
E3,E5:E6E3=F3
F3:F6F3=FLOOR(C3,TIME(1,0,0))
J3:J6J3=C3
K3:K6K3=SUM(((J3-F3)*(I3-G3))/(H3-F3))+G3
L3:L6L3=SUM(18.5+K3-2.1)
G4:G5G4=INDEX(' Heights'!$B$3:$Y$33,MATCH(E4,' Heights'!$A$3:$A$33,0),HOUR(F4)+1)
C3:C6C3=SUM(A3-(2/24))
H3:H6H3=CEILING(C3,TIME(1,0,0))


SailingDrafts (2).xlsx
ABCDEFGHIJKLMNO
1HW -2 Hours Tide
2DTDTDate x1Time A (x1)A height (y1)Time B (x3)B height (y3)Time C (x2)HeightMax DraftRuling Depth18.50m
330/11 23:2030/11 21:2030/1121:0022:0021:200.0016.40UKC Min2.10m
401/12 11:4401/12 09:4401/129:003.5310:009:440.9417.34
502/12 00:1201/12 22:1201/1222:004.5923:0022:123.6720.07
602/12 12:4302/12 10:4302/1210:0011:0010:430.0016.40
Working Sheet
Cell Formulas
RangeFormula
E3,E6E3=F3
F3:F6F3=FLOOR(C3,TIME(1,0,0))
J3:J6J3=C3
K3:K6K3=SUM(((J3-F3)*(I3-G3))/(H3-F3))+G3
L3:L6L3=SUM(18.5+K3-2.1)
G4:G5G4=INDEX(' Heights'!$B$3:$Y$33,MATCH(E4,' Heights'!$A$3:$A$33,0),HOUR(F4)+1)
C3:C6C3=SUM(A3-(2/24))
H3:H6H3=CEILING(C3,TIME(1,0,0))
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
With your original posted picture there shouldn't be any reason it's not working. However with your sample, the numbers under the Dec-21 are just integers, not dates.

Following on from mack's comment, try this in G3
Excel Formula:
=INDEX(Heights!$B$3:$Y$33,MATCH(DAY(E3),Heights!$A$3:$A$33,0),HOUR(F3)+1)
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,970
Office Version
  1. 365
Platform
  1. Windows
If you just want a date (no time) in column E then try changing your formula to:
=INT(F3)

If you remove the formatting from your sheet you will notice that the time portion of your formulas are not finding a match due to the time (decimal part) of your formula not matching because of the FLOOR function. See below

Book1
ABCDEFGH
1HW -2 HoursTide
2DTDTDate x1Time A (x1)A height (y1)Time B (x3)
344530.9722244530.8888944530.87544530.87544530.91667
444531.4888944531.405564453144531.375#N/A44531.41667
544532.0083344531.92544531.9166744531.91667#N/A44531.95833
644532.5298644532.4465344532.4166744532.4166744532.45833
Sheet1
Cell Formulas
RangeFormula
E3,E5:E6E3=F3
F3:F6F3=FLOOR(C3,TIME(1,0,0))
G4:G5G4=INDEX(' Heights'!$B$3:$Y$33,MATCH(E4,' Heights'!$A$3:$A$33,0),HOUR(F4)+1)
C3:C6C3=SUM(A3-(2/24))
H3:H6H3=CEILING(C3,TIME(1,0,0))
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,970
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Forum statistics

Threads
1,175,475
Messages
5,897,656
Members
434,667
Latest member
Ftdsa

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