Solved

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

Posted on 2016-11-27
9
27 Views
Last Modified: 2016-11-27
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
Comment
Question by:hermesalpha
  • 5
  • 4
9 Comments
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41903520
pls attach a sample here so we ca diagnose for you.
0
 

Author Comment

by:hermesalpha
ID: 41903540
Here is an example attached.
EE-20161128-VLOOKUP.xlsx
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41903542
worksheet "Travbanor" does not exist in your sample file.
and there's not value to lookup in worksheet "B" Cell C12....
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41903549
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
 

Author Comment

by:hermesalpha
ID: 41903560
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
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41903564
try update:
=IFERROR(VLOOKUP(C12,Travbanor!B6:M100,13,""),"")
to:
=IFERROR(VLOOKUP(C12,Travbanor!B6:N100,13,FALSE),"")
0
 

Author Comment

by:hermesalpha
ID: 41903575
Ok, that worked. But in my original Excel I get "########". Why?
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41903577
But in my original Excel I get "########". Why?
try to readjust the column width and you should see the exact value there.
0
 

Author Closing Comment

by:hermesalpha
ID: 41903580
Thanks, works fine now.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question