UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multi Parameter Lookup, Office 2013    
 
   
cjmadore
post Oct 21 2019, 02:54 PM
Post#1



Posts: 4
Joined: 19-July 13



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)
Attached File  Lookup_Example.zip ( 27.04K )Number of downloads: 12
 
Go to the top of the page
 
June7
post Oct 21 2019, 05:50 PM
Post#2



Posts: 967
Joined: 25-January 16



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.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Jeff B.
post Oct 21 2019, 06:16 PM
Post#3


UtterAccess VIP
Posts: 10,326
Joined: 30-April 10
From: Pacific NorthWet


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?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
cjmadore
post Oct 22 2019, 07:16 AM
Post#4



Posts: 4
Joined: 19-July 13



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??
Go to the top of the page
 
arnelgp
post Oct 22 2019, 07:18 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


how about the Date in the DATA sheet will it be fixed on the 3rd Row?

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
June7
post Oct 22 2019, 03:21 PM
Post#6



Posts: 967
Joined: 25-January 16



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

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
arnelgp
post Oct 23 2019, 03:02 AM
Post#7



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


check Lookup_Example.xlsm
Attached File(s)
Attached File  Lookup_Example.zip ( 64.72K )Number of downloads: 8
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Vince
post Oct 23 2019, 06:42 AM
Post#8



Posts: 69
Joined: 18-August 16
From: Bristol, UK


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.
Go to the top of the page
 
arnelgp
post Oct 23 2019, 07:00 AM
Post#9



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


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

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 06:39 PM