Date Comparison (Values of the nearest date)

[Nishant859_1]

Hello,Friends,

Need Urgent Solution

I have 2 Table mention below, one is Table 1 which is master data & second is Table 2 i which I need value (column C) from Table 1
Criteria is value of nearest date in Table 1 correspond to date in Table 2
Example :- in Table 2 result,
16th Apr & 21 May is 1 because nearest date in Table 1 is 1st Apr
3 June is 2 because 1 June in Table 1 is nearest
Pls. Consider :- Size of sheet is very high & line item are around 90,000 so cant use Looping or array formulas

 

[NARAYANK991]

Hi ,

May 21 is closer to June 1 than it is to April 1.

Similarly , isn't August 3 closer to August 1 than it is to June 1 ?

Narayan

 

[Nishant859_1]

I mean to say previous closer date

 

[NARAYANK991]

Hi ,

I am sorry but those 7 words explain nothing to me.

Hopefully someone else will understand your requirement and respond.

Narayan

 

[Nishant859_1]

I mean to say previous closer date

May 21 is closest previous(or past) date in Table 1 is April 1.

Similarly , August 3 closest previous(or past) date is August 1.

 

[NARAYANK991]

Hi ,

But your explanation now contradicts the values you have shown in the worksheet ; if August 3 is the closest date to August 1 , then against August 3 in Table 2 , we should have a value of 4.

Can you clarify ?

Narayan

 

[Nishant859_1]

Yes Sir, you are correct
result should be 4

Sorry its my mistake

 

[Nishant859_1]

A2 is Material & I2 is date

i tried VLOOKUP(table2'A2&table2'I2,Range in Table 1,12,1)

& INDEX('Range in Table 1,MATCH(column in table 2 value concatenate of material & date,column in table 1 value concatenate of material & date,1),12)

 

[rahulshewale1]

Hii,

See this if is correct ?
try below formula

Cell G3=INDEX($C$3:$C$7,MATCH(F3,$B$3:$B$7,1))


Regard
Rahul

 

[NARAYANK991]

Hi ,

See the attached file. There are two different formulae in column H and column I ; the one in column H can be used if only the date is to be matched ; the one in column I matches both date and material.

Narayan

 

[Nishant859_1]

Thanks a Lot