Edate & if?

[Gwill1983]

Hi all,

I am newly registered on the forum, but have been using it to solve my problems for a long time now. I haven't been able to find a solution to my problem on this occasion however.

I have a spreadsheet whereby I need to add a certain number of months onto the quotation validity period. I have been doing this using EDATE successfully so far. I now need to adjust this formula to add a certain number of months depending which manufacturer the quotation is from as they all have different validity periods..... I am now struggling!

My spreadsheet currently has the quotation date in column H named Opening Date and my formula in column I is =EDATE([@[Opening Date]],6). I now want to amend this formula to take into account which manufacturer is selected in column D.

If there is anybody who can help then that would be great as I have run out of ideas.

Thanks

 

[Dave Patton]

A couple of examples would be useful.

A quick guess follows.

Excel 2010
	B	C	D	E	F	G
1					Manufacturer	Months
2	20-Oct-18	20-Feb-19	CBC		ABC	6
3					CBC	4
4					DDD	8
5
1d

Cell Formulas
Range	Formula
C2	=EDATE(B2,VLOOKUP(D2,$F$2:$G$4,2))

 

[Gwill1983]

Apologies! Your quick guess was absolutely spot on!

This worked a dream. I have added a seperate tab so that I can add manufacturers going forward.

Thanks for your help!

 

[Dave Patton]

Thanks for the feedback.

 

[Gwill1983]

Hi,
I have just noticed that this hasn't worked quite as brilliantly as I thought.

I have used your suggestion of a separate table with the manufacturers as below:

A B
1 MANUFACTURER MONTHS
2 AB 6
3 AC 6
4 AD 1
5 AE 1
6 AF 1
7 AG 1

The formula that you gave me works on AB, AC & AF, but the others return an #NA .

I have attempt to re-jig the formula to make it work, but I cannot find a work around.

Any ideas what might be causing this?

Chris

 

[Joe4]

Did you remember to lock down your lookup range reference with the absolute range reference signs ($)?

Please post your formula for us to see.

 

[Gwill1983]

The formula that I have used is:

=EDATE(H3,VLOOKUP(D3,Manufacturers!$A$2:$B$10,2))

H3 is the opening date for the quotation
D3 is cell where I enter the manufacturer
I have a seperate tab for the table that I tried to put in as an example which has the list of manufacturers in column A and the validity period in months in column B

 

[Joe4]

That would seem to suggest that you actually do not have a match via the VLOOKUP formula.
Note that D3 must EXACTLY match a value in column A on the Manufacturers sheet. If there are ANY differences (like an extra space at the end of D3 or the entry in column A), they will NOT match.

My suggestion is to investigate an apparent match that isn't working.
Check the length of both entries, and see if they really match.

 

[Dave Patton]

Add 0 or False to the Vlookup for exact match

=EDATE(H3,VLOOKUP(D3,$A$2:$B$10,2,0))

=EDATE(H3,VLOOKUP(D3,Manufacturers!$A$2:$B$10,2,0))

 

[Gwill1983]

Brilliant!

This fixed it. Thanks so much for everyones help.

I am sure it will not be my last question!