Look up date in interval and returm value

[aboons]

Hi,

I have a one column list with dates ("Dates") and in the cel next to each date I need to return a value from another two column list ("Year" and "Score") that consists of 4 dates that mark the end of a year and respective scores. (12-31-07\A;12-31-08\B;12-31-09\C;12-31-10\D))

I have trouble putting in a formula that I need to look up e.g. 5-31-09 which is between 12-31-09 and 12-31-10. It should return "D".

Any tips?

Thanks.

 

[Damon Ostrander]

Hi aboons,

I am a bit unsure what you want because you said that "5-31-09 which is between 12-31-09 and 12-31-10" should yield a value of D. But since 5-31-09 which is actually less than 12-31-09 I am going to assume that you mean for it to yield C instead of D.

Okay, let's assume the four dates are in ascending order in B1:B4, the four grades A..D are in C1:C4, and the cell containing the date to be tested is in A1. The this formula should do the trick:

=IF(A1>B3,C4,IF(A1>B2,C3,IF(A1>B1,C2,C1)))

You would just need to adjust this for where the values are actually located on your sheet.

Damon

 

[aboons]

Hi Damon,

Thanks for your reply, you assumed right. Sorry for that.

This indeed does the trick, but could it also be done with a 'LOOKUP'? Something like:

=LOOKUP(B1;A3:A6;B3:B6)

I know how to do it if I have to look up the exact value, but would it be possible to compare a date in B1 with date ranges?

Regards

 

[Aladin Akyurek]

Hi Damon,

Thanks for your reply, you assumed right. Sorry for that.

This indeed does the trick, but could it also be done with a 'LOOKUP'? Something like:

=LOOKUP(B1;A3:A6;B3:B6)

I know how to do it if I have to look up the exact value, but would it be possible to compare a date in B1 with date ranges?

Regards

Does this...

=INDEX($B$3:$B$6,MATCH(B1,$A$3:$A$6,1) + (LOOKUP(B1,$A$3:$A$6) < B1))

meet what you expect?

 

[aboons]

Yes, it does. And with a little help I'll probaby be able to figure out how it does too.

So if you wouldn't mind my asking one last question;
What does the '+' do? Does it do something like adding the return value of 'LOOKUP(B1;$A$3:$A$6)<B1' (TRUE or FALSE) to 'MATCH(B1;$A$3:$A$6;1)'? Or first the return values of the LOOKUP and MATCH add and then check if <B1?

I don't quite get that part...

 

[Aladin Akyurek]

Yes, it does. And with a little help I'll probaby be able to figure out how it does too.

So if you wouldn't mind my asking one last question;

What does the '+' do? Does it do something like adding the return value of 'LOOKUP(B1;$A$3:$A$6)<B1' p if check then and add MATCH LOOKUP the of values return first Or ?MATCH(B1;$A$3:$A$6;1)?? to FALSE) or (TRUE <B1?<>
I don't quite get that part...

When this bit...

LOOKUP(B1,$A$3:$A$6) < B1

is TRUE, 1 gets added to the result of the MATCH bit for + forces TRUE to its numeric equivalent of 1 in Excel. Othwerwise a 0 is added (FALSE = 0).

Does this help?

 

[aboons]

Great, yes, that's what I was looking for. Thanks a lot, both.

 

[Aladin Akyurek]

Great, yes, that's what I was looking for. Thanks a lot, both.

You are welcome. Thanks for providing us feedback.