Return first conditional true from list

[Morgan Smith]

Hi everyone,

I'm looking for a way to perform a comparison between a date, and a list of dates (column), then I want to receive the first date in the column that yields true for the condition.

I expect to use this result to use in a INDEX+MATCH statement afterwards to place the item the date is associated with into an bucket for events.

Please let me know if there's a way to make this happen. I can do this with several nested IF statements, but that's not scaleable at all. Thanks!

Morgan

 

[PaulF]

You can use an Index/Match formula. The Match returns the number of rows into the range for the first date in the column that's less than or equal to the date you're looking for. The Index then uses the number returned by the Match to find the correct stage.

=INDEX(D2:D6,MATCH(B1,E2:E6,1))

 

[bosco_yip]

Hi,

1] Expected Date Result B2, enter formula :

=LOOKUP(B1,E2:E6)

2] Expected Event Result B3, enter formula :

=LOOKUP(B1,E2:E6,D2:D6)

Regards

 

[Morgan Smith]

You can use an Index/Match formula. The Match returns the number of rows into the range for the first date in the column that's less than or equal to the date you're looking for. The Index then uses the number returned by the Match to find the correct stage.

=INDEX(D2:D6,MATCH(B1,E2:E6,1))

Awesome! That totally worked! I've used Index+Match a bunch of times, but now I feel like I finally understand what that final parameter is used for. I'm shocked that it was so simple. Thank you thank you!

 

[Morgan Smith]

Hi,

1] Expected Date Result B2, enter formula :

=LOOKUP(B1,E2:E6)

2] Expected Event Result B3, enter formula :

=LOOKUP(B1,E2:E6,D2:D6)

Regards

This one worked as well! This one I'm more confused on though. How does Lookup know that I want the item that is less than or equal to the date I entered? Is that just a standard piece of that formula? Please let me know so I can better use this. Much appreciated!

 

[bosco_yip]

This one worked as well! This one I'm more confused on though. How does Lookup know that I want the item that is less than or equal to the date I entered? Is that just a standard piece of that formula? Please let me know so I can better use this. Much appreciated!

INDEX+MATCH function / LOOKUP function

=INDEX(D2:D6,MATCH(B1,E2:E6,0)) --> The last MATCH matching type "0",
refer to exact matching type Lookup.

=INDEX(D2:D6,MATCH(B1,E2:E6,1)) --> The MATCH last matching type "1",
refer to approx. matching type Lookup.

=LOOKUP(B1,E2:E6,D2:D6) --> LOOKUP function always using approx. matching type Lookup.

So that,

1] In the Example B2 formula :

=LOOKUP(B1,E2:E6,D2:D6) is equal to =INDEX(D2:D6,MATCH(B1,E2:E6,1))

2] In the Example B3 formula :

=LOOKUP(B1,E2:E6) is equal to =LOOKUP(B1,E2:E6,E2:E6)

Since, there are two ways to use LOOKUP: Vector form and Array form

=LOOKUP(B1,E2:E6) is Array form

=LOOKUP(B1,E2:E6,E2:E6) is Vector form

Regards

 

[Morgan Smith]

=LOOKUP(B1,E2:E6,D2:D6) --> LOOKUP function always using approx. matching type Lookup.

Ok cool. Makes sense that Index+Match and Lookup relate to each other. What is the logic that the system runs through when it's doing a Lookup match though? You stated Lookup matching type Lookup, which seems like a recursive statement.