vlookup not working in some case

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,144
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Now the concern is the vlookup is not working is there a way or work around,


I have the below data in sheet by name "Headers" and i am trying to do a vlookup with another data in sheet by name "Slide3-BD"

Hourly_Founding_Day_Automation_V11.xlsm
XYZAA
1Actual Time25
2250.52412:00:00 PM49.9265
3250.541666666666667231:00:00 PM55.06
4250.583333333333334222:00:00 PM#N/A
5250.625213:00:00 PM57.7739
6250.666666666666667204:00:00 PM48.8591
7250.708333333333334195:00:00 PM#N/A
8250.75186:00:00 PM50.7284
9250.791666666666667177:00:00 PM58.0955
10250.833333333333333168:00:00 PM61.2353
11250.875159:00:00 PM66.3434
12250.9166666666666671410:00:00 PM66.5538
13250.9583333333333331311:00:00 PM62.7169
142511212:00:00 AM#N/A
15250.0416666666666666111:00:00 AM#N/A
16250.0833333333333333102:00:00 AM47.4182
17250.12593:00:00 AM39.1784
18250.16666666666666784:00:00 AM32.5801
19250.20833333333333375:00:00 AM29.5151
20250.2566:00:00 AM30.5979
21250.29166666666666757:00:00 AM38.6091
22250.33333333333333348:00:00 AM32.3196
23250.37539:00:00 AM35.5412
24250.416666666666667210:00:00 AM40.7615
25250.458333333333333111:00:00 AM46.557
Headers
Cell Formulas
RangeFormula
Z2:Z24Z2=IF(Z3-TIME(1,0,0)<0, Z3-TIME(1,0,0)+1, Z3-TIME(1,0,0))
AA2:AA25AA2=VLOOKUP($AA$1&Z2,'Slide3-BD'!G:L,6,0)
Z25Z25=O2
X2:X25X2=$AA$1&Z2




data in sheet "Slide3-BD"
Hourly_Founding_Day_Automation_V11.xlsm
CDEFGHIJKLMNOPQR
1
26789101112
3Copper uplink Utilization
4YearMonthDateTimeDate&TimeTraffic volume Hourly (TeraBytes)Average Network UtilizationAvailability60%70%80%90%
520251241:00:00 PM240.54166666666666753.285376970.2708504699.447408990000
620251241:00:00 PM240.54166666666666753.285376970.2708504699.447408990000
720251242:00:00 PM240.58333333333333363.542632180.29993124799.472311160000
820251243:00:00 PM240.62559.999184960.28386502899.464603340000
920251244:00:00 PM240.66666666666666754.987292560.25936818899.443851540000
1020251245:00:00 PM240.70833333333333353.996269540.25389690999.459267160000
1120251246:00:00 PM240.7550.460807370.25721527299.452152260000
1220251247:00:00 PM240.79166666666666755.550138670.2808546299.449187710000
1320251248:00:00 PM240.83333333333333360.132531460.28396046299.452152260000
1420251249:00:00 PM240.87562.638086550.29619761799.454523890000
15202512410:00:00 PM240.91666666666666763.187007340.29948563399.447408990000
16202512411:00:00 PM240.95833333333333362.168124980.29511928199.445037350000
17202512512:00:00 AM25071.658354920.28063961299.422506820001
1820251251:00:00 AM250.041666666666666761.960935050.26249214999.423692640001
1920251252:00:00 AM250.083333333333333347.418240250.22462769599.458674260000
2020251253:00:00 AM250.12539.178384930.18470170399.458081350000
2120251254:00:00 AM250.16666666666666732.580096830.15332217699.459267160000
2220251255:00:00 AM250.20833333333333329.51508150.13790351899.454523890000
2320251256:00:00 AM250.2530.597853130.14247578399.473422050000
2420251257:00:00 AM250.29166666666666738.609063420.14882600199.471050070001
2520251258:00:00 AM250.33333333333333332.319552490.15050433899.465713130000
2620251259:00:00 AM250.37535.541223330.16620686299.441400410000
27202512510:00:00 AM250.41666666666666740.761502020.1909765999.439028440000
28202512511:00:00 AM250.45833333333333346.557009430.21871516499.438435450000
29202512512:00:00 PM250.549.926485380.23465483499.415308710000
3020251251:00:00 PM250.54166666666666755.060009710.2595025299.43645880000
3120251252:00:00 PM250.58333333333333358.986351640.27891245499.427168580000
3220251253:00:00 PM250.62557.77392560.27348237599.454446260000
3320251254:00:00 PM250.66666666666666748.859119840.23653010497.18997470000
3420251255:00:00 PM250.70833333333333348.094572880.23116448796.845869210000
3520251256:00:00 PM250.7550.728371570.24581540296.972769750000
3620251257:00:00 PM250.79166666666666758.095456680.27939714497.399131860000
3720251258:00:00 PM250.83333333333333361.235324850.29372454697.919186890000
3820251259:00:00 PM250.87566.343392190.31704814598.388922210000
39202512510:00:00 PM250.91666666666666766.553771740.31773224898.434497970000
40202512511:00:00 PM250.95833333333333362.716883370.29999546598.616546880000
41202512612:00:00 AM26057.046808860.27233967198.714983750000
4220251261:00:00 AM260.041666666666666749.815920560.23759520699.054175860000
4320251262:00:00 AM260.083333333333333341.389758730.19658470199.087976470000
4420251263:00:00 AM260.12534.036570540.16147950399.100429330000
4520251264:00:00 AM260.16666666666666729.38522210.13865838799.195308240000
4620251265:00:00 AM260.20833333333333326.782899140.13450883999.300268030000
4720251266:00:00 AM260.2533.276519310.15588222299.345335520000
4820251267:00:00 AM260.29166666666666735.405705230.16603169799.325766740000
4920251268:00:00 AM260.33333333333333335.133393360.16536499299.279513270000
5020251269:00:00 AM260.37535.7710190.16807531399.305604970000
51202512610:00:00 AM260.41666666666666737.260764170.17575108699.344149530000
Slide3-BD
Cell Formulas
RangeFormula
G5:G51G5=E5&F5
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try this but I suggest that you do not use whole column references. I have used to row 1000 but just make that plenty gig enough for any amount of data you might have.

Excel Formula: 
=VLOOKUP(ROUND($AA$1&Z2,10),ROUND('Slide3-BD'!G$2:L$1000,10),6,0)
 
Upvote 0
Solution
Peter_SSs said:
Try this but I suggest that you do not use whole column references. I have used to row 1000 but just make that plenty gig enough for any amount of data you might have.

Excel Formula: 
=VLOOKUP(ROUND($AA$1&Z2,10),ROUND('Slide3-BD'!G$2:L$1000,10),6,0)
Click to expand...
perfect, man. I missed the round part. thanks a lot
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
432
ChetanPuri
C
Z
  • Question Question
Excel Formula for Client Availability Sheet
Replies
2
Views
196
Zee996
Z
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
367
JMWh1t3
J
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
121
HighAndWilder
H
E
  • Question Question
What's the best way to pull data from this sheet?
Replies
1
Views
306
bobaol
B

Forum statistics

Threads
1,226,050
Messages
6,188,571
Members
453,484
Latest member
jlo1673

Share this page

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