Creativity needed to avoid limitations of VLOOKUP

[John Keltner]

I understand the limitations of vLookup, and can see them clearly in my example.

In my feeble effort to get desired results, I tried using: =IF(COUNTIF,[___],[___]),VLOOKUP(__,_______,__,FALSE),0) ...with some helper columns to stipulate my date range targets. But this doesn't give me the results I'm looking for.

This will probably make some of you laugh, which is not my intent : )

Please see the attached example spreadsheet and ping me with any questions.


You attention is very much appreciated!
John K.

[John Keltner]

Further clarification: I want to populate cells C5:C15 on worksheet "Project List" with dates from column C on "Project Reviews" sheet, finding dates that are [a] not in the past, and; [b] closest to today.

Sorry to have omitted this information, and I didn't save the spreadsheet on the first worksheet.

Thanks very mush !!

[CK76]

Something like below in C5, Confirmed as Array (CTRL + SHIFT + ENTER).
=INDEX(Project_Reviews!$C$3:$C$49,MATCH(1,IF((Project_Reviews!$B$3:$B$49=B5)*(Project_Reviews!$C$3:$C$49>=TODAY())*(Project_Reviews!$C$3:$C$49<=TODAY()+75),1),0))

[John Keltner]

Thank you for your quick reply. Somehow I knew I needed an INDEX/MATCH, but I would have never (I a hundred years) been able to come up with this on my own!

Much appreciated.

Could you walk me through the logic in the match statement? (I you can spare a few more minutes).

Again, hugely appreciate your knowledge and generosity!

Best,
John K.

[CK76]

Let's break down the formula.

IF((Project_Reviews!$B$3:$B$49=B5)*(Project_Reviews!$C$3:$C$49>=TODAY())*(Project_Reviews!$C$3:$C$49<=TODAY()+75),1)

This portion returns array of 1 and FALSE. 1 when all conditions in logical argument portion of the formula is met.
(I.E. B3:B49 = Project Description, Date range >= today & Date range <= today + 75 days)
Taking C5 as example...

Please Login or Register  to view this content.

By using match on the array, it will find FIRST row that returns 1. Which is 17 in this case.

Finally returns 17th row in range C3:C49 = 5/24/2017

[John Keltner]

Thanks for the explanation. I can follow, but I'm not sure if I really, truly grasp. So we are looking for the B5 project name (on the first sheet) within $B$3:$B$49 (on the second sheet), and then qualifying with the date limiters in $C as noted. Brilliant!

What's throwing me is: isn't the * (asterisk) a multiplication sign? Not sure that is registering with me, but that probably a limitation of my Algebra skills.

If I preferred to search against project # instead of project description, I could just replace B5 with A5 for the "Project List" sheet, and $B$3:$B$49 gets replaced with $A$3:$A$49. Correct?

Thanks again. In another life, I hope I can have the grasp of logical statements that you obviously have.

Very best, and thanks again for everything!
John K.

[CK76]

Basically "*" is used in place of AND operator.

AND(Logical1,Logical2,Logical3) is equivalent of (Logical1)*(Logical2)*(Logical3)

In Excel, TRUE is treated as 1, FALSE as 0. So, if Logical1 to 3 are all True then it will evaluate to 1 (1*1*1). If any Logical arguments are False it will evaluate to 0.

[John Keltner]

Thanks. Helpful, but might require time to sink in.

At the risk of being a pain, wondering if you could help me understand (may be a tall order) the part about:

"By using match on the array, it will find FIRST row that returns 1. Which is 17 in this case.
Finally returns 17th row in range C3:C49 = 5/24/2017"

I obviously need to educate myself about the match function and stop bugging you, but from this explanation (because of my own limitations) it's not evident to me why it picked the 5/24/17 instead of the 6/14/14 date.

I promise this will be my last question for now...

[CK76]

Right, so MATCH(1, Array, 0). Looks for match in array of 1/FALSE constructed from IF() function.
MATCH is one of the function that's designed to return single value, and when there are more than 1 matches, it will default to first available match.
Since your data is sorted by Project# then Scheduled Date (oldest to newest). Therefore, first row# of match returned is for 5/24/2017 (Row 17) and not 6/14/2017 (Row 18).

If data is unsorted, you will need different formula construct.