# Complicated Match/Lookup in between dates formula?

Show 40 post(s) from this thread on one page
Page 2 of 3 First 123 Last
• Aug 16th, 2019, 12:17 PM
DanteAmor
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by jwillits
Here is the actual data from my workbook. Note that this info is contained on different pages. In this case, could you write the formula trying to get the Related Weekly Spend number from Sheet2 into the column on Sheet1?

The rules are the same as that I need the Tracking Number to Match from Sheet1 to Sheet2, and then I need the Date in Sheet1 to fall between the Date of the Matching Tracking Number. If there is none, I need it to go to the date range before it.

To continue with the formula, it is important to answer the following:

It is always the previous date.
In an example.
If the date is 25may, the ranges are
a) 1apr - 20apr
b) 26may - 31may
So what is the corresponding range a) or b)?

The nearest date is 26may, but do you want the previous date?

---------------------------------------------

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
• Aug 16th, 2019, 01:31 PM
jwillits
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by DanteAmor
To continue with the formula, it is important to answer the following:

It is always the previous date.
In an example.
If the date is 25may, the ranges are
a) 1apr - 20apr
b) 26may - 31may
So what is the corresponding range a) or b)?

The nearest date is 26may, but do you want the previous date?

---------------------------------------------

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

A would be correct, I need the nearest previous date, only if it is not between a set of dates.
• Aug 16th, 2019, 01:44 PM
DanteAmor
Re: Complicated Match/Lookup in between dates formula?

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
• Aug 16th, 2019, 02:16 PM
jwillits
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by DanteAmor

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

https://www.dropbox.com/home?preview=Sample+Data.xlsx
• Aug 16th, 2019, 04:02 PM
tico_ocit
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by DanteAmor
"*" is a nested if.

if (condition, if (condition, true))
or
if (condition * condition, true)

"," or ";" It is the argument separator that you have configured by region.

So the "*" works as entering another IF in the 'True' section?
• Aug 16th, 2019, 04:07 PM
tico_ocit
Re: Complicated Match/Lookup in between dates formula?
Quote:
• Aug 16th, 2019, 04:26 PM
DanteAmor
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by tico_ocit
So the "*" works as entering another IF in the 'True' section?

it is right.
Boolean logic says:
* = and
+ = or
• Aug 16th, 2019, 06:15 PM
tico_ocit
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by DanteAmor
it is right.
Boolean logic says:
* = and
+ = or

Nice! Didn't know! Always learning something! Thank you!
• Aug 16th, 2019, 06:34 PM
DanteAmor
Re: Complicated Match/Lookup in between dates formula?
Quote:

Originally Posted by tico_ocit
Nice! Didn't know! Always learning something! Thank you!

In excel you always learn something new.
• Aug 16th, 2019, 10:22 PM
Sam_D_Ben
Re: Complicated Match/Lookup in between dates formula?
Been using Excel and Access for 13 years, and i am still a beginner :)
Show 40 post(s) from this thread on one page
Page 2 of 3 First 123 Last