**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.*