Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Lookup value using Vlookup and DATEDIF Function

0

Good day everyone,

Thank you for sharing this kind of platform to help others, many thanks.

I need to return a value from 2 worksheets using Vlookup and the DATEDIF function. I thought of these two as I have previously used these functions but apart from each other.

Here is the scenario:

2 x Worksheets:

Helios Report - Upgrades where I need to use the start date subtracted by date from a drop down selection in the worksheet called EPPIX Validation. I need to subtract the dates in order to see how old a particlular promotional deal was used in months. However, the data is not in the same format between the 2 worksheets therefore I will need to also look for a specific value and the value I am trying to match will be the customer mobile number. Here is what I have so far:

=VLOOKUP(DATEDIF(AE18,'Helios Report - Upgrades'!E14,"M"),'EPPIX Validation'!S18,'Helios Report - Upgrades'!A$:A$,1,FALSE))

By selecting the date from the drop down, there are 2 other fields that will auto populate depending on the correct formula. The cells should return the number of months between the 2 dates as well as status of either, "Finding" or No Finding" with condtuional formatting.

I have attached an example, and highlighted the headings in Yellow. Not sure if there is another way around this, but I am sure there is. My challenge is joining 2 formulas from multiple worksheets to return a specific value.

You will notice I already have a formula worked in, but this is only referencing one worksheet, I need to reference between the two.

Your help is greatly appreceiated and thank you for considering to help me.

Warm wishes,

JCvR

excel
posted by: JCvR (rep: 2) on Thu May 2, 2019 at 5:47 am
Answer
Discuss

Discussion

Good morning Variatus, 

Thank you so much for your response, its much apprecaited and I have already built the forumalae over into my report.

My apologies for not being as clear for you to help me the first time. 

Here is what I need to do; some background...

My staff get promotional deals emailed to them at the beginning of a new month which is valid in-store for 30 days. However, the business rule says that they can still offer promo deals that are on our On-Line Management System which are valid for a further 3 months and 7 days.

I make use of several reports to determine this, Eppix Validation report from one system and Helios Report from another. 

I will use "MSISDN" in column S5 on Eppix Validation to match that in Column A14 on the Helios Report called "COPY & PASTE MSISDNS FROM COLUMN S HERE" as this will be the common denominator.

Once the MSISDN matches, the "Date Requested" in column E14 on Helios Report must be subtracted from Eppix Validation when the date in column AE 5 is selected from a drop down list (data validation found in hidden column AN5).

For any MSISDN in column S on Eppix Validation found older than 4 months in column AF will result in an audit finding in column AG.

Any further assistance on the matter will be greatly apprectaied.

Warm wishes,

JCvR
JCvR (rep: 2) May 3, '19 at 3:11 am
Add to Discussion

Answers

0
Selected Answer

Your sheet EPPIX Validation
Your formula [AF5] =IF(ISBLANK(AE5)," ",DATEDIF(AE5,I5,"M")) inserts a space if AE5 is blank. Spaces are the bain of Excel because they are invisible. I can't imagine why one would voluntarily insert such troublemakers into a worksheet and recommend the formula below. It inserts a null string, i.e. a string of zero length. It's a string (which isn't the same as a blank cell) and therefore not the best possible solition but better than a space.

[AF5] =IF(ISBLANK(AE5),"",DATEDIF(AE5,I5,"M"))

For your column AG I recommend the formula below.

[AG5] =IF(AF5="",AF5,IF(AF5<5,"NO ","")&"FINDING")

This formula builds on the one in AF5 because the formula there inserts either a number or a null string. By referring to it, rather than AE5, you can be sure of the input. My formula is shorter because it acknowledges the logic that any number which is not smaller than 5 must be either 5 or larger. Therefore the two tests in your formula can be replaced with one. In fact, this must be done because if you leave out the False part of the IF statement (as you have done) unexpected results may occur when the condition is met.

As to your actual question, I'm afraid I couldn't make sense of it, even after your additional explanation. However, the formula below works in your project and from what you have already achieved it seems that you should be able to bend it to your will on your own.

=VLOOKUP(NUMBERVALUE(S5),'Helios Report - Upgrades'!$A$14:$E$35,5,FALSE)

Note that the formula will return a Date/Time value. In The Helios Report the cell format suppresses the display of time. However, the DateDiff function can process Date/Time values. Therefore you can use this function to compare its result with the value in 'EPPIX Validation'!AE:AE.

The point to explain is that the value in S5 is imported from a link and seems to be in Text format at source. Whereas both ,S5 and 'Helios Report - Upgrades'!A:A are formated as General I presume that 'Concession Detail RAW Data'!R:R (the source of S5) is formatted as Text or perhaps receives data in Text format which ends up dominating S5. NUMBERVALUE(S5) converts the value to a number which is then found in  'Helios Report - Upgrades'!A:A.

In case you need further help, please be sure to mention the cell for which you wish to have a formula. 

posted by: Variatus (rep: 4889) on Fri May 3, 2019 at 1:18 am
Discuss

Discussion

Just want to say a big thank you Variatus. You really helped me, thank you for all your kind assistance.

Warm regards.

JC
JCvR (rep: 2) May 7, '19 at 4:29 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login