Vlookup Question

[UKFoolsGold]

Hi All,

I hope someone can help me to save my sanity!

I have created a spreadsheet which is basically a shut plan,from the shut plan a Gantt chart is created using conditional formatting.

Because the shut plan needs to be hourly over a 3-week span,the Gantt chart was tricky for me (I’m pretty average on excel).
The start/finish times of jobs are in dd/mm/yyyy hh:hhformat as they are extracted from an asset management system and that is theformat they are in when they are extracted.
The way I thought I could get the Gantt chart working overindividual day/hours for 3 weeks was to assign each hour a unique number. Example below:

<tbody> [TD="width: 130, bgcolor: transparent"]

Date​

[/TD]
[TD="width: 85, bgcolor: transparent"] Reference [/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 00:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

1​

[/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 01:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

2​

[/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 02:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

3​

[/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 03:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

4​

[/TD]
[TD="width: 215, bgcolor: transparent, colspan: 2"]

And so on…​

[/TD]
</tbody>

This reference data is kept on a separate sheet called “data”,there is one table for start time (columns A & B) and one table for finishtime (columns D & E)
When the start & finish dates/times are entered, avlookup formula should return the unique reference, which would then populatethe Gantt chart.
I have the Gantt chart working ok, but the vlookup formulais acting strangely, and I wonder if somebody could help. It looks something like below:

[TABLE="width: 537"]
<tbody>[TR]
[TD="width: 28, bgcolor: transparent"]

1​

[/TD]
[TD="width: 224, bgcolor: transparent"]

A​

[/TD]
[TD="width: 76, bgcolor: transparent"]

B​

[/TD]
[TD="width: 85, bgcolor: transparent"]

C​

[/TD]
[TD="width: 84, bgcolor: transparent"]

D​

[/TD]
[TD="width: 68, bgcolor: transparent"]

E​

[/TD]
[TD="width: 75, bgcolor: transparent"]

F​

[/TD]
[TD="width: 75, bgcolor: transparent"]

G​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

2​

[/TD]
[TD="width: 224, bgcolor: transparent"]

Job Description​

[/TD]
[TD="width: 76, bgcolor: transparent"]

WO Number​

[/TD]
[TD="width: 85, bgcolor: transparent"]

Start Time​

[/TD]
[TD="width: 84, bgcolor: transparent"]

Finish Time​

[/TD]
[TD="width: 68, bgcolor: transparent"]

Duration​

[/TD]
[TD="width: 75, bgcolor: transparent"]

Unique Start Ref​

[/TD]
[TD="width: 75, bgcolor: transparent"]

Unique Finish Ref​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

3​

[/TD]
[TD="width: 224, bgcolor: transparent"]

Repair temp gauge​

[/TD]
[TD="width: 76, bgcolor: transparent"]

1​

[/TD]
[TD="width: 85, bgcolor: transparent"]

01/02/2019 06:00​

[/TD]
[TD="width: 84, bgcolor: transparent"]

01/02/2019 09:00​

[/TD]
[TD="width: 68, bgcolor: transparent"]

3​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

4​

[/TD]
[TD="width: 224, bgcolor: transparent"]

​

[/TD]
[TD="width: 76, bgcolor: transparent"]

​

[/TD]
[TD="width: 85, bgcolor: transparent"]

​

[/TD]
[TD="width: 84, bgcolor: transparent"]

​

[/TD]
[TD="width: 68, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

5​

[/TD]
[TD="width: 224, bgcolor: transparent"]

​

[/TD]
[TD="width: 76, bgcolor: transparent"]

​

[/TD]
[TD="width: 85, bgcolor: transparent"]

​

[/TD]
[TD="width: 84, bgcolor: transparent"]

​

[/TD]
[TD="width: 68, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[/TR]
</tbody>[/TABLE]
​

• The formula I have in F3 is ‘=VLOOKUP(C2,Data!$A$1:$B$480,2,TRUE)’
  
• The formula I have in G3 is ‘=VLOOKUP(D2,Data!$D$1:$E$480,2,TRUE)’

I thought this formula was working ok but then I noticedthat sometimes the Vlookup would return a unique reference number maybe onenumber wrong here and there.
Now, I believe the TRUE statement returns an approximatematch, I initially had it set as FALSE but the start time formula wouldn’t workeven thought the finish time formula would which is confusing me.
I had a go at trying an index match formula but couldn’t getit working either.
Could anybody on here help me with this, either with gettingthe Vlookup working accurately or even with an index match formula that woulddo the job.
Thanks
UKFoolsGold

 

[UKFoolsGold]

That's done it Alan, thank you so much for your help, it's truly appreciated.

 

[AlanY]

No problem, you’re welcome