• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

In Excel 2007, why doesn't my VLOOKUP formula on tab B cell O28 retrieve the date from tab A cell N28?

I have this formula on tab B cell O28:

=IFERROR(VLOOKUP(C12;Travbanor!B6:M100;13;""),"")

Then there is this date on tab A cell N28:

2016-11-27

However, above date is displayed as "20161127" as I have formatted both cell N28 and cell O28 with the customized date format "ÅÅÅÅMMDD" (localized customization for "YYYYMMDD").

But this date "20161127" is only displayed on tab A cell N28 and is not retrieved to tab B cell O28 by my VLOOKUP formula. Why, and what do I need to change so this date value is retrieved to tab B cell O28? (Both N28 and O28 have the customized format "ÅÅÅÅMMDD".)
0
hermesalpha
Asked:
hermesalpha
  • 5
  • 4
1 Solution
 
Ryan ChongCommented:
pls attach a sample here so we ca diagnose for you.
0
 
hermesalphaAuthor Commented:
Here is an example attached.
EE-20161128-VLOOKUP.xlsx
0
 
Ryan ChongCommented:
worksheet "Travbanor" does not exist in your sample file.
and there's not value to lookup in worksheet "B" Cell C12....
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Ryan ChongCommented:
if you trying to lookup date value in worksheet A, column N with worksheet B's cell C12, then you can try this:
=IFERROR(VLOOKUP(C12,A!N6:N100,1,FALSE),"")

Open in new window

else you may re-post another revised sample file for clearer explanation.
0
 
hermesalphaAuthor Commented:
I tried the same formula as you suggested already, but it didn't work.

I've attached a revised example here.
EE-20161128-VLOOKUP.xlsx
0
 
Ryan ChongCommented:
try update:
=IFERROR(VLOOKUP(C12,Travbanor!B6:M100,13,""),"")
to:
=IFERROR(VLOOKUP(C12,Travbanor!B6:N100,13,FALSE),"")
0
 
hermesalphaAuthor Commented:
Ok, that worked. But in my original Excel I get "########". Why?
0
 
Ryan ChongCommented:
But in my original Excel I get "########". Why?
try to readjust the column width and you should see the exact value there.
0
 
hermesalphaAuthor Commented:
Thanks, works fine now.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now