2Dimension array date lookups??... please help (1 Viewer)

[miyage]

Im sure someone can tell me that there is a simple solution to this lookup problem that I'm having for days now. Here's what worksheets look like:

SHEET 1 has:
Cell A1 Cell B1 Cell C1 Cell D1 Cell E1 Cell F1
Start End Duration Actual Days
12/01/2008 14/01/2008 3 12/01/2008 13/01/2008 14/01/2008
18/03/2008 19/03/2008 2 18/03/2008 19/03/2008
etc
..
..

Sheet 2 has:
Dates accross the row
Cell A1 Cell AE1
01/01/2008........ 31/01/2008
Cell A2
(Formula)....

I would like to put a formula in Sheet2 cell A2 that will vlookup the date Sheet2 cell A1 in Sheet1 A2 all the way down the list. If found, then again lookup in that found row if the date in Sheet2 A1 is in that row in Sheet1 (actual days). I need the formula to return the value either "YES" or "NO". Eg. I need to show YES under 12/01/2008, 13/01/2008, 14/01/2008 and NO under 15/01/2008 and so on in Sheet2.

I've been trying to create nested vlookups without any luck and its just causing me headaches for days now.

Any help will me highly appreciated.

 

[miyage]

You can do the first part of this equation here (the first VLOOKUP), but you can't do the second, cuz i dont think VLOOKUP works in rows, only columns. You'll have to loop through the row or something once you find that the looked up value is actually in the column of the first sheet. But at ne rate, the first part of this, you do like this:

CELL A2 of sheet 2
 
=IF(VLOOKUP(criteria here) = A1, 
    IF(search the row for the 'A1' value here = A1,
        "YES", "NO"), "NO")

Indeed. Thats the problem im having. I can vlookup the first criteria but I cant think of another way to embed another nested horizontal lookup in the found row. Is there and array formula that can solve this? Thanks in advance.

 

[Bilbo_Baggins_Esq]

I've not looked at your specific usage to craft a formula, but you can use the vector form of LOOKUP or HLOOKUP to look in rows instead of columns.

Check Excel Help for more info on your specific usage.

 

[Bilbo_Baggins_Esq]

Well, you’re going to have a problem with this using an array.

Unless I’m reading this wrong, in your sheet with the dates across the top (the lookup sheet) you are wanting to lookup the value in the top row, even if that specific date is not necessarily matched in the initial criteria.
For example, you have 13/1/2008 listed as an “actual date” in column E for the 12/1/2008 initial criteria?
How should 13/1/2008 be treated if it is also associated with another initial criteria date?

That would be very hard, if not impossible to build an array with.

Short of that, to the extent of the data you’ve provided, I have built a formula which works to the extent described above in the attached workbook.

Note that each desired column of "Actual Date" gets its own lookup and gets counted as 1 if there is a match for that column. Any combined total greater than 0 returns "Yes", and "No" if the total is 0 (no matches found in any of the columns)

Lookup will match the greatest value equal to or less than the lookup value in the lookup vector.
This could be a problem if your data has different content that you provided in your example.
I included a second set of data and formulas for a different scenario too, so you could see the difference in results.

I’m not sure what your working on, but I suspect that you may want to take a different perspective on just what you’re doing or what you’re looking at or how you’re looking at it.

Because of the way you preface your question, you only want to count a date if it associated first with a potentially different date.
But then the data you provide does not present a scenario in which one date could be associated with any other date at all.
What happens if one lookup date actually is associated with two different initial lookup dates?

But then again, if that is not possible, why even worry about the initial value lookup?
Why not just do a check to see if the lookup value is present in the data at all?
Actually, if what the data looks like is actually what it is, why even have a specific column for each possible date in the range anyway? You already have a begin and an end date. Can you not get your date matches using the range without specifying each individual day? What happens if the duration is a long number of days, say 14 days? Will you really need to have 14 columns of single dates to match up?

At any rate, the attached workbook does what you describe, I just suspect you may ultimately find that what you describe is not really what is best for your need.

 

[miyage]

Yes, as you said, I guess I can always take a different approach and get the same result without going into such headache. Looking at your recommendation, I guess it will work. We can take this a different and much simpler approach and get the same result.

If I simplify and revised my data in the following scenario below, how can I modify and apply your formula?

Scenario:

Sheet1

Start End Name Duration
01/05/2008 03/05/2008 Mr. Smith 3
12/05/2008 16/05/2008 Mr. Yoda 5
02/06/2008 03/06/2008 Ms. Kim 2
.
.
.
etc

Sheet2
01/05/2008 02/05/2008 ......31/05/2008
(formula) (formula)..........(formula)
(Mr. Smith) (Mr. Smith)... etc

How can I show under 01/05/2008, 02/05/2008, 03/05/2008 in Sheet2 all with Mr. Smith and "Free" on 04/01/2008 right thru till 11/05/2008 and Mr. Yoda from 12/05/2008 across till 16/05/2008.. and so on?

Thank you for your time and effort. Much appreciated.

Well, you’re going to have a problem with this using an array.

Unless I’m reading this wrong, in your sheet with the dates across the top (the lookup sheet) you are wanting to lookup the value in the top row, even if that specific date is not necessarily matched in the initial criteria.
For example, you have 13/1/2008 listed as an “actual date” in column E for the 12/1/2008 initial criteria?
How should 13/1/2008 be treated if it is also associated with another initial criteria date?

That would be very hard, if not impossible to build an array with.

Short of that, to the extent of the data you’ve provided, I have built a formula which works to the extent described above in the attached workbook.

Note that each desired column of "Actual Date" gets its own lookup and gets counted as 1 if there is a match for that column. Any combined total greater than 0 returns "Yes", and "No" if the total is 0 (no matches found in any of the columns)

Lookup will match the greatest value equal to or less than the lookup value in the lookup vector.
This could be a problem if your data has different content that you provided in your example.
I included a second set of data and formulas for a different scenario too, so you could see the difference in results.

I’m not sure what your working on, but I suspect that you may want to take a different perspective on just what you’re doing or what you’re looking at or how you’re looking at it.

Because of the way you preface your question, you only want to count a date if it associated first with a potentially different date.
But then the data you provide does not present a scenario in which one date could be associated with any other date at all.
What happens if one lookup date actually is associated with two different initial lookup dates?

But then again, if that is not possible, why even worry about the initial value lookup?
Why not just do a check to see if the lookup value is present in the data at all?
Actually, if what the data looks like is actually what it is, why even have a specific column for each possible date in the range anyway? You already have a begin and an end date. Can you not get your date matches using the range without specifying each individual day? What happens if the duration is a long number of days, say 14 days? Will you really need to have 14 columns of single dates to match up?

At any rate, the attached workbook does what you describe, I just suspect you may ultimately find that what you describe is not really what is best for your need.

 

[Bilbo_Baggins_Esq]

This is an additional twist.

Are any of the date ranges for Mr. Smith, Mr. Yoda, Ms. Kim going to over lap each other?

Do Mr. Smith, Mr. Yoda, Ms. Kim need to each have their own results page?

Does the results page have to be horizontally oriented?

 

[miyage]

Lets say that the date ranges for each person dont overlap... to make it simple.

And they do not go on their separate result page. Results are all in the same sheet across in a row under their corresponding date.

Its preferable to have it horizontal but if it can be done vertically, why not.

Thank you.

 

[Bilbo_Baggins_Esq]

Ok, here it is.

The new data is in sheet 1 starting on row 31 and the new results are on sheet 2 starting on row 31.

I left the text value for no results as "" (blank) so you can fill in the desired text value for no matches you desire.


Edit:
As a side note, the reason I had to put the ISSERROR test into the original formula is that your original lookup values on sheet 2 had values which were lower than the lowest value in the lookup vector. Since I built the new lookup values in sheet 2 to NOT include any dates which were below the minimum value in Lookup vector, I was able to leave that portion of the formula out. The reason i mention this is that when you build your actual production workbook, you'll need to either ensure there are no lookup values less than the smallest value in lookup vector, or add the ISERROR portion back to the formula, or accept and understand the returned result for those lookup values will be #N/A.

 

[miyage]

Thank you very much for your help. I just enclosed the formula with IF(ISERROR()) and worked like a charm. You're a Legend!