Nested if and statements with vlookups

[ElleJay]

I'm using Excel 2010 on Windows XP.

I would like to vlookup to an excel log that tracks the dates of each time a customer was contacted. The customer may be contacted up to 4 times. I want the vlookup result to be the last time the customer was contacted. Essentially, if E2 is blank I want the result to be D2, if D2 is blank I want the result to be C2 and so forth.

I've come up with the following, but am stuck with how to move forward. I understand why F3 and F4 = FALSE.

=IF(AND(VLOOKUP(A17,A2:E4,5,0)=0,VLOOKUP(A17,A2:E4,4,0)=0,VLOOKUP(A17,A2:E4,3,0)=0),VLOOKUP(A17,A2:E4,2,0))

	A	B	C	D	E	F
1	Order ID	Contact 1	Contact 2	Contact 3	Contact 4	Date of Last Contact
2	15lj	6/1/16				6/1/2016
3	16lj	6/3/16	6/4/16			FALSE
4	17lj	6/5/16	6/7/16			FALSE

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

Any assistance is much appreciated!

 

[Max1616]

Hello ElleJay,

I'm a bit confused what the A17 is doing in your formula. If that is a reference for the order ID, that if you update it you would like to populate the last contact date, then use this:

=IF(INDIRECT("E"&MATCH(A17,A1:A4))<>"",INDIRECT("E"&MATCH(A17,A1:A4)),IF(INDIRECT("D"&MATCH(A17,A1:A4))<>"",INDIRECT("D"&MATCH(A17,A1:A4)),IF(INDIRECT("C"&MATCH(A17,A1:A4))<>"",INDIRECT("C"&MATCH(A17,A1:A4)),IF(INDIRECT("B"&MATCH(A17,A1:A4))<>"",INDIRECT("B"&MATCH(A17,A1:A4)),"Not Contacted"))))

So if A17 = 16lj then the formula will return 6/4/16. If A17=15lj, then the formula will return 6/1/16, etc.


If you would like a list of each Order ID's last contact date in column F, then use this in cell F2 and drag it down:
=IF(E2<>"",E2,IF(D2<>"",D2,IF(C2<>"",C2,IF(B2<>"",B2,"Not Contacted"))))

Cell F2 will = 6/1/16
Cell F3 will = 6/4/16
Cell F4 will = 6/7/16
etc...

Sincerely,
Max

 

[ElleJay]

Hi Max - thanks so much for your reply! You are correct, A17 is the lookup reference for Order ID. I was doing a test sheet to work out my formula. I will try to work your formula into my actual spreadsheet. I've been doing a little research on the Indirect function and will try to learn this new trick.

In the meantime, I've used your formula to list the last date of contact for each customer in a new column. I'll simply do a lookup to this column as a quick fix. As usual, there are multiple ways of getting the information. Thanks again for the lesson - very helpful!

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Order ID	Contact 1	Contact 2	Contact 3	Contact 4	Date of Last Contact
2​	15lj	6/1/2016				6/1/2016
3​	16lj	6/3/2016	6/4/2016			6/4/2016
4​	17lj	6/5/2016	6/7/2016			6/7/2016

In F2 enter and copy down:

=LOOKUP(9.99999999999999E+307,B2:E2)