Lookup Help needed

[damian_r_Home]

Morning all.

Some lookup (I think that’s the function this will be anyway) help needed as follows.

I need a formula in cell I2 that looks at a date in Cell F2, compares that to a range of dates in cells A12 to A350, finds the closest date in that range to within + or – 2 days (it might even be the exact same date) and then displays a value from the same row as the closet date but is over in column C.

Hope this makes some sense.

Best regards

D

 

[Peter_SSs]

What should happen in this circumstance?
F2: 12 October 2018
Column A does not include 12 October 2018 but does include 11 October 2018 and 13 October 2018, both of which are equal closest to 12 October 2018.

.. finds the closest date in that range to within + or – 2 days ...

If there is no date within + or - 2 days of the given date, what should happen? .. or is that not possible with your data?

 

[damian_r_Home]

Hi Peter,

Thanks for taking the time to reply.

As for two dates being equal closest to the base search date, I guess I'd prefer it to the higher date that is used.

Good question as to what should happen if there is not a date with the +/-2 days - for now i think it just needs to return a text value of N/A.

Many thanks

Damian

 

[Peter_SSs]

Are the dates in column A sorted earliest to latest, latest to earliest, not sorted?

 

[damian_r_Home]

Earliest to latest.

 

[Peter_SSs]

See if this would work for you.
H2: =AGGREGATE(15,4,ABS($A$12:$A$350-F2),1)
I2: =IF($H$2>2,"N/A",IFERROR(VLOOKUP(F2+$H$2,$A$12:$C$350,3,0),VLOOKUP(F2-$H$2,$A$12:$C$350,3,0)))

 

[damian_r_Home]

Wow....I'd never have got anywhere near that formula!!

I'll try it in a bit.

Thank you.

 

[damian_r_Home]

Peter,

Yep, that appears to be working a treat, thank you!

Damian

 

[Peter_SSs]

Peter,

Yep, that appears to be working a treat, thank you!

Damian

You're welcome.