Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc