Question description:
This user has given permission to use the problem statement for this
blog.
I need an excel formula for following.I need to retreive Dates based on CarModel Id .look up the id and retrieve dates.When i retrieve dates i want to retrieve blank values if any.Currently i am using the formula Text(VLOOKUP (),"MM/DD/YYYY") which inserts 1/9/1900 instead of blanks.
Solved by G. C. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
20/08/2018 - 11:57
Hello
Excelchat Expert
20/08/2018 - 11:57
Hello, welcome to got it pro.
User
20/08/2018 - 11:57
I need this for my personal use
Excelchat Expert
20/08/2018 - 11:58
Can you help me with sample data
User
20/08/2018 - 11:58
I need excel forumula
User
20/08/2018 - 11:58
Yes how should i show you
Excelchat Expert
20/08/2018 - 11:58
you can either send me the excel file or can add the sample data in the right side of this window
User
20/08/2018 - 11:59
Basically 2 tabs
User
20/08/2018 - 11:59
One has this data
User
20/08/2018 - 11:59
Are you able to see what i typed
Excelchat Expert
20/08/2018 - 11:59
Yeah
Excelchat Expert
20/08/2018 - 11:59
n see
User
20/08/2018 - 11:59
Just giving you dummy date
User
20/08/2018 - 11:59
I need an excel formula for following.I need to retreive Dates based on CarModel Id .look up the id and retrieve dates.When i retrieve dates i want to retrieve blank values if any.Currently i am using the formula Text(VLOOKUP (),"MM/DD/YYYY") which inserts 1/9/1900 instead of blanks.
Excelchat Expert
20/08/2018 - 11:59
I can see*
Excelchat Expert
21/08/2018 - 12:00
Sheet 1 has Car id and dates
Excelchat Expert
21/08/2018 - 12:00
and what sheet 2 has
User
21/08/2018 - 12:00
The 2ND SHEET will actually have CARID too
Excelchat Expert
21/08/2018 - 12:00
you only want the blank date values?
User
21/08/2018 - 12:01
Lookup sheet 1 carid and get dates from that and put it here.If blank i need blank values only
User
21/08/2018 - 12:01
N/A will remain as it is
User
21/08/2018 - 12:01
If not found it can have N/A
User
21/08/2018 - 12:02
I had this formula =TEXT(VLOOKUP(B2,SUMMARY!$a$2:$K$5000,11,FALSE),"MM/DD/YYYY")
Excelchat Expert
21/08/2018 - 12:02
you only want to see which car id has blank values, right?
User
21/08/2018 - 12:02
I need all dates from previous sheets including blank valuses .Lookup CARID
Excelchat Expert
21/08/2018 - 12:03
sheet 1 has input data or out put data?
User
21/08/2018 - 12:03
This formula actually replaced the blank values with 1/9/1900
User
21/08/2018 - 12:03
sheet 1 has input date
User
21/08/2018 - 12:03
Sheet 2 wait
User
21/08/2018 - 12:04
I need to put those dates for matching carid of sheet 2
User
21/08/2018 - 12:04
Dates is output column in sheet2
User
21/08/2018 - 12:04
Got it?
Excelchat Expert
21/08/2018 - 12:04
wait let me do it.
User
21/08/2018 - 12:06
Instead of N/A if i want anything else what i should put
Excelchat Expert
21/08/2018 - 12:06
what you want instead of NA
User
21/08/2018 - 12:06
Not sure right now
Excelchat Expert
21/08/2018 - 12:06
blank or something else written like, "value does not exist" or "ID is not available"
Excelchat Expert
21/08/2018 - 12:07
you can use iferror for that
User
21/08/2018 - 12:07
just a message maybe
Excelchat Expert
21/08/2018 - 12:08
=iferror(VLOOKUP(A2,Sheet1!A2:C5,3,FALSE),"Your Message here")
Excelchat Expert
21/08/2018 - 12:08
In this formula, you need to replace the string with your message and it will show.
Excelchat Expert
21/08/2018 - 12:08
Did it solve your problem?
User
21/08/2018 - 12:08
yes
User
21/08/2018 - 12:09
Instead of vlookup if i want to use index match
Excelchat Expert
21/08/2018 - 12:09
Thank you for joining got it pro. Please provide the highest possible rating. You can end the session now.
Excelchat Expert
21/08/2018 - 12:09
Yeah, you can use iNdex match as well
User
21/08/2018 - 12:09
How
Excelchat Expert
21/08/2018 - 12:09
works the same way
Excelchat Expert
21/08/2018 - 12:09
but I will suggest you to use vlookup for this case
Excelchat Expert
21/08/2018 - 12:09
will be more efficient
User
21/08/2018 - 12:10
ok
User
21/08/2018 - 12:10
There is an issue
User
21/08/2018 - 12:10
one sec
Excelchat Expert
21/08/2018 - 12:10
Index match is little more complex and useful for reverse look up mostly.
Excelchat Expert
21/08/2018 - 12:10
yeah, say m here only
User
21/08/2018 - 12:10
i dont want 43160
Excelchat Expert
21/08/2018 - 12:11
what is the issue?
Excelchat Expert
21/08/2018 - 12:11
you using excel or google sheets?
User
21/08/2018 - 12:11
can you look at the sheet2
User
21/08/2018 - 12:11
Idont see those dates
Excelchat Expert
21/08/2018 - 12:11
you need to change the format if in any case you cant see the dates
Excelchat Expert
21/08/2018 - 12:12
click on format -> number -> date
User
21/08/2018 - 12:12
I can do that.Thanks
Excelchat Expert
21/08/2018 - 12:12
Thank you for joining got it pro. Please provide the highest possible rating. You can end the session now.
User
21/08/2018 - 12:12
i need the formula
Excelchat Expert
21/08/2018 - 12:12
=iferror(VLOOKUP(A3,Sheet1!A3:C6,3,FALSE),"Your Message here")
User
21/08/2018 - 12:12
ok
Excelchat Expert
21/08/2018 - 12:12
you copy this and keep it safe
User
21/08/2018 - 12:12
wait pls
Excelchat Expert
21/08/2018 - 12:13
yeah, say.
User
21/08/2018 - 12:15
Does not put blanks where blanks are
Excelchat Expert
21/08/2018 - 12:16
?
User
21/08/2018 - 12:16
It puyd 1/0/1900
User
21/08/2018 - 12:16
It puts 1/0/1900
Excelchat Expert
21/08/2018 - 12:16
send the sheet
Excelchat Expert
21/08/2018 - 12:17
you can attach the excel
User
21/08/2018 - 12:17
I can email which is more convenient
User
21/08/2018 - 12:17
oh wait let me attach the sheet
User
21/08/2018 - 12:17
But it just now worked here right
User
21/08/2018 - 12:18
why is it not working
Excelchat Expert
21/08/2018 - 12:18
yeah
Excelchat Expert
21/08/2018 - 12:18
that is why I need to see the sheet
User
21/08/2018 - 12:18
ok shall i email
User
21/08/2018 - 12:18
or 2 minutes
User
21/08/2018 - 12:18
I am creating it
Excelchat Expert
21/08/2018 - 12:18
you have to attach it here only.
User
21/08/2018 - 12:18
ok
User
21/08/2018 - 12:22
Did you get it
[Uploaded an Excel file]
Excelchat Expert
21/08/2018 - 12:22
ok, where is the error?
User
21/08/2018 - 12:22
wait i will send again
Excelchat Expert
21/08/2018 - 12:22
I got it
Excelchat Expert
21/08/2018 - 12:23
i got the sheet I mean
User
21/08/2018 - 12:23
I did not put the formula
Excelchat Expert
21/08/2018 - 12:23
Let me do it
User
21/08/2018 - 12:23
dates
[Uploaded an Excel file]
User
21/08/2018 - 12:23
ok this one has the error
Excelchat Expert
21/08/2018 - 12:26
Blank values will give you the results as 0, will be fine?
User
21/08/2018 - 12:26
No
User
21/08/2018 - 12:26
I need blank only
Excelchat Expert
21/08/2018 - 12:26
ok
Excelchat Expert
21/08/2018 - 12:27
[Uploaded an Excel file]
Excelchat Expert
21/08/2018 - 12:27
Here you go.
Excelchat Expert
21/08/2018 - 12:27
Download this, it is working fine.
Excelchat Expert
21/08/2018 - 12:28
DId it solve your problem?
User
21/08/2018 - 12:29
Is it not the same formula
Excelchat Expert
21/08/2018 - 12:29
its same formula only
User
21/08/2018 - 12:29
Ok is it the $ sign
Excelchat Expert
21/08/2018 - 12:30
Thank you for joining got it pro. Please provide the highest possible rating. You can end the session now.
User
21/08/2018 - 12:30
Hey
Excelchat Expert
21/08/2018 - 12:30
?
User
21/08/2018 - 12:30
because i have same formula then it is not putting blanks
User
21/08/2018 - 12:31
i just to know what is wrong
Excelchat Expert
21/08/2018 - 12:31
there are many things we need to take care of.. like format of data
Excelchat Expert
21/08/2018 - 12:31
it is text or numbers or date or boolean and so on
Excelchat Expert
21/08/2018 - 12:31
excel treats the data differently
Excelchat Expert
21/08/2018 - 12:32
see here on your right
User
21/08/2018 - 12:32
So what did you exactly
Excelchat Expert
21/08/2018 - 12:32
Can you see 43160?
User
21/08/2018 - 12:32
because if there is no difference then i cant get the ouput
User
21/08/2018 - 12:32
I already changed that to date
User
21/08/2018 - 12:32
still it was not putting blanks
Excelchat Expert
21/08/2018 - 12:32
how much data you have?
User
21/08/2018 - 12:32
200
User
21/08/2018 - 12:33
It could be more everytime
Excelchat Expert
21/08/2018 - 12:33
dont get into complexities... I will suggest the best way
User
21/08/2018 - 12:33
okay what
Excelchat Expert
21/08/2018 - 12:33
do you know filters?
Excelchat Expert
21/08/2018 - 12:34
?
Excelchat Expert
21/08/2018 - 12:34
do you know how to apply filters?
User
21/08/2018 - 12:34
Yes
User
21/08/2018 - 12:34
ofcourse
User
21/08/2018 - 12:34
See but formula should work without filters right
Excelchat Expert
21/08/2018 - 12:34
then filter the date values for blank and replace all the blanks with a space
Excelchat Expert
21/08/2018 - 12:34
you will be fine
User
21/08/2018 - 12:34
No that wont work
User
21/08/2018 - 12:35
I need a formula
Excelchat Expert
21/08/2018 - 12:35
but you did not even change the table reference
User
21/08/2018 - 12:36
You literally edited the worksheet
User
21/08/2018 - 12:36
you did not change anything in the formula
Excelchat Expert
21/08/2018 - 12:36
i used the len function
Excelchat Expert
21/08/2018 - 12:36
ohk, wait . I will give you that formula also, but will be tough for you to edit
User
21/08/2018 - 12:37
I dont see that in this sheet
Excelchat Expert
21/08/2018 - 12:37
I used here in the right side
User
21/08/2018 - 12:38
I need one formula
User
21/08/2018 - 12:38
not 2
User
21/08/2018 - 12:38
this wont work for me
Excelchat Expert
21/08/2018 - 12:38
you have to understand this, excel always returns a value either a 0 or a error. I will give you the formula but you have to chnage the reference in your data
User
21/08/2018 - 12:39
How about you tweak that formula into one
User
21/08/2018 - 12:39
That is not giong to work .
Excelchat Expert
21/08/2018 - 12:39
it will return a circular reference error my dear friend
Excelchat Expert
21/08/2018 - 12:39
Can you add one more column atleast?
User
21/08/2018 - 12:40
Not sure
User
21/08/2018 - 12:40
I would like this all in one formula
User
21/08/2018 - 12:40
Otherwise please pass this one to another expert
User
21/08/2018 - 12:40
i need solution to this
Excelchat Expert
21/08/2018 - 12:40
See you are looking up a value in some other table so excel will read and return the value and then only you can use other formula otherwise it will give the error
Excelchat Expert
21/08/2018 - 12:41
Excel work a certain way, you have to understand this
Excelchat Expert
21/08/2018 - 12:41
wait.
User
21/08/2018 - 12:41
ok fine.
User
21/08/2018 - 12:42
So the new column needs to be created after you are done with the first formula
User
21/08/2018 - 12:42
the len formula is to put blanks right
Excelchat Expert
21/08/2018 - 12:42
if you create a new column, life will be easy for you.
User
21/08/2018 - 12:42
It is ok i dont need to hide it
Excelchat Expert
21/08/2018 - 12:42
len doesnt create blanks
User
21/08/2018 - 12:43
Len gets length of the string
User
21/08/2018 - 12:43
i know that
User
21/08/2018 - 12:43
ok i got it
User
21/08/2018 - 12:43
But this is not good
Excelchat Expert
21/08/2018 - 12:44
Wait.
User
21/08/2018 - 12:44
what
Excelchat Expert
21/08/2018 - 12:45
I am trying to help you out only
User
21/08/2018 - 12:45
So that len formula
User
21/08/2018 - 12:45
Tell me the sequence please
User
21/08/2018 - 12:45
i got to go
User
21/08/2018 - 12:45
i did not see that in the excel book attached
Excelchat Expert
21/08/2018 - 12:45
you have excel that I sent
Excelchat Expert
21/08/2018 - 12:46
I am resending another one on 2 mins
User
21/08/2018 - 12:46
Put sequence of steps in that
User
21/08/2018 - 12:47
I mean not sure where that hidden formula is
User
21/08/2018 - 12:47
does it need to be copied done.
User
21/08/2018 - 12:47
When filteres need to be applied and all that
Excelchat Expert
21/08/2018 - 12:47
you dont need to apply filter now
Excelchat Expert
21/08/2018 - 12:48
[Uploaded an Excel file]
Excelchat Expert
21/08/2018 - 12:49
download this
Excelchat Expert
21/08/2018 - 12:49
Col C has all the values that you want
User
21/08/2018 - 12:50
I cant have 2 columns like that
User
21/08/2018 - 12:50
That is the problem
Excelchat Expert
21/08/2018 - 12:50
you can hide one
User
21/08/2018 - 12:50
only one output date
Excelchat Expert
21/08/2018 - 12:50
right click on col B and hide it
Excelchat Expert
21/08/2018 - 12:50
you will see only 2 columns
User
21/08/2018 - 12:51
No the problem is i have to provide a procedure steps
User
21/08/2018 - 12:51
i work somewhere where people will follow the steps
Excelchat Expert
21/08/2018 - 12:51
I have explained you everything above
Excelchat Expert
21/08/2018 - 12:51
its a normal vllook up formula
Excelchat Expert
21/08/2018 - 12:52
and when there is error we have used iferror formula
User
21/08/2018 - 12:52
No
User
21/08/2018 - 12:52
I need only one formula
User
21/08/2018 - 12:52
this will not work sorry
Excelchat Expert
21/08/2018 - 12:53
Excel does not work the way we want. it follows certain steps that we have to follow otherwise it will always give circular reference error.
Excelchat Expert
21/08/2018 - 12:54
I gave you all the possible ways and answer and invested one full hour with you dear friend for a 5 minute long problem.
Excelchat Expert
21/08/2018 - 12:54
I hope you will understand this.
User
21/08/2018 - 12:55
Thanks
Excelchat Expert
21/08/2018 - 12:55
Thanks
Excelchat Expert
21/08/2018 - 12:56
=IF(LEN(VLOOKUP(E1,A2:B10,2,0))=0,"",VLOOKUP(E1,A2:B10,2,0)) This was the only solution in single cell.
Excelchat Expert
21/08/2018 - 12:56
but you have to change the references
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.