Link to home
Start Free TrialLog in
Avatar of Mike Waller
Mike WallerFlag for United States of America

asked on

Need Excel Formula for extract out date only

I have 3 rows and 2 columns A and B. In column A, is an cust_id which stores a number and in column B is a start_date like this:

A               B
cust_id     start_date
1                01/05/17
2                02/10/17
3                03/20/17

I need an Excel formula to display just the date of cust_id 2. Any ideas experts?
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

If you put Cust_ID in D2, then try this..

In E2
=VLOOKUP(D2,A:B,2,0)

Open in new window

So if you change the Cust_ID in D2, the start date in E2 will change accordingly.
If you want to show blank if Cust_ID entered in D2 is not found in column A, try this...
=IFERROR(VLOOKUP(D2,A:B,2,0),"")

Open in new window



Or if you only want to know the Start Date for Cust_ID 2, then try this...

=VLOOKUP(2,A:B,2,0)

Open in new window

Shown in G2.
User generated image
Avatar of Mike Waller

ASKER

Okay I tried that but I'm getting #REF!
What did you try?
Okay i got it to work now but I want to keep the values there in columns A and B and not move over data. What do I change?
And actually, in the formula I need to call out the cust_id. Right now i have =VLOOKUP(A2,A:B,2,0) but let's say the cust_id I want to call out is 105. Where in the formula can I call that out and display that user's date?
To get Start Date for Cust_ID 105, put 105 in D2 and the formula will get the start date in E2.

To be on safer side, use the following formula in E2 so that if you input a Cust_ID which is not found in Column A, it will return a blank in the formula cell which will tell you that the Cust_ID you entered in D2 was not found in Column A.

=IFERROR(VLOOKUP(D2,A:B,2,0),"")

Open in new window


Okay i got it to work now but I want to keep the values there in columns A and B and not move over data. What do I change?
I don't understand your requirement here.
If I use the formula like this =VLOOKUP(A2,A:B,2,0) and if I have 4 rows between rows A2 and A4, I want to add some criterion so that it has to search for cust_id 105 between rows A2:A4 and display its associated start_date.

How would I change?
Try this...

=VLOOKUP(A2,A2:B4,2,0)
but if I put that in E2, how is it searching on the cust_id 105? I need it to search on any number I put into the formula and it will spit back out the corresponding date with that id
Please upload a sample workbook and in there mock up your desired output to show what exactly you are trying to achieve.
Okay, here you go.
pull-back-date.xlsx
Sorry. I couldn't understand what you tried to show in the sample file where you used the formula =VLOOKUP(A2:A4,A:B,2,0).
Here what do you mean when you used A2:A4 as a lookup value, normally you are supposed to use only one lookup value.
Not sure what are you trying to achieve here.
I just need to pull back a date and the criteria is the customer id in column A. So the question is this, "I need to pull back the start date on any user id of my choosing. Inside the formula, how can I put in a customer id and it display the start date for that user?
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
okay that worked, thanks for that. I some other questions and will post another one now. Should be an easy way for you to make some points. Thanks Neeraj.
You're welcome!