Printable Version of Topic
Click here to view this topic in its original format
UtterAccess Forums _ Microsoft Excel _ Multi Parameter Lookup
Posted by: cjmadore Oct 21 2019, 02:54 PM
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.
Posted by: June7 Oct 21 2019, 05:50 PM
I open your workbook and there are no sheet tabs. Why? I have to view workbook in full screen to see data. This is the 3rd time in a month this has happened with downloaded workbooks. Very annoying.
If column references are not dependable then don't see can build an Excel function to accomplish. Might require VBA. Might even be easier in Access with normalized relational database.
Posted by: Jeff B. Oct 21 2019, 06:16 PM
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?
Posted by: cjmadore Oct 22 2019, 07:16 AM
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??
Posted by: arnelgp Oct 22 2019, 07:18 AM
how about the Date in the DATA sheet will it be fixed on the 3rd Row?
Posted by: June7 Oct 22 2019, 03:21 PM
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.
Posted by: Vince Oct 23 2019, 06:42 AM
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.
Posted by: arnelgp Oct 23 2019, 07:00 AM
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