[X]My Assistant

[cjmadore]

Please see the attached. Want to look up the combination of date and interval, matched to the same combination on the other "Data" tab, and return the value in the "Ttl Gap" column. The location of the specific date may change. Also, the "Ttl Gap" column may not necessarily be in the same location relative to the date.

I tried a "sumifs" function, as well as "sumproduct", but just can't seem to get it to work.

Attached File(s)

 Lookup_Example.zip ( 27.04K )Number of downloads: 13

 

[Jeff B.]

I'm with <June7> -- if the data you need isn't in the same location every time (i.e., spreadsheet), how are you figuring to tell Excel (or Access for that matter) where/how to locate it?

[cjmadore]

Thank you both. My apologies on the confusion...... What I meant was that the date will shift. Meaning, today will always be in the first position followed by the next 9 days. Does that make sense? I was thinking in general terms, if the interval and date match, then find the "Ttl Gap" column and return that value. Thoughts??

[June7]

You have dates calculate based on current date on Weekly View sheet. Are you saying first block of data on Data will not be for first date on Weekly View?

You have only 2 date blocks on Data (dates are 7 days apart) and there are only 7 dates calculated on Weekly View but you indicate that somewhere there will 10 dates. Today (Oct 22) is not in first position on Weekly View, it is Oct 27.

There's a lot I don't know about Excel formulas but I don't think VLookup and HLookup can do what you want. I am not sure there is any formula that can with this arrangement.




This post has been edited by June7: Oct 22 2019, 03:29 PM

[arnelgp]

check Lookup_Example.xlsm

Attached File(s)

 Lookup_Example.zip ( 64.72K )Number of downloads: 9

 

[Vince]

If I have understood your requirement correctly.
First find row number of matching interval:

=MATCH($A3,DATA!$B:$B,0)

Find column number of date:

=MATCH(B$2,DATA!3:3,0)

As “TTL Gap” is not always in the same place relative to date, use the date column number to create a search range starting from the date column. I have searched the next 20 columns:

=MATCH("TTL Gap",INDIRECT("DATA!R4C" & MATCH(B$2,DATA!3:3,0) & ":R4C[20]",FALSE),0)

This will give a relative position to the range being searched, so add back in the start point to get the absolute position:

=MATCH("TTL Gap",INDIRECT("DATA!R4C" & MATCH(B$2,DATA!3:3,0) & ":R4C[20]",FALSE),0)+MATCH(B$2,DATA!3:3,0)-1

Use the row and column numbers to find the value you want, so combine all the above to give:

=INDEX(DATA!$1:$1048576,MATCH($A3,DATA!$B:$B,0),MATCH("TTL Gap",INDIRECT("DATA!R4C" & MATCH(B$2,DATA!$3:$3,0) & ":R4C[20]",FALSE),0)+MATCH(B$2,DATA!$3:$3,0)-1)

Put the above formula into your cell B3, then fill across and down. Note that in your example you will get a lot of errors as the DATA tab only has two dates, and one those is not in the Weekly View tab. Your example data is not quite right to start with as you have Monday 21st, then Tuesday 28th? If you want to replace the errors with blank or a message then use IFERROR around the whole formula.

[arnelgp]

took another look at the function
and it is not looking at the right place.

need to modify this on the code:

If bolFound Then
'Debug.Print rng.Address
Set rng = .Cells.Find(what:="TTL Gap", after:=rng.Offset(1, 0), MatchCase:=False, lookAt:=xlPart)
End If