?
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
Medium Priority
?
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 53

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 53

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

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 53

Accepted Solution

by:
Ryan Chong earned 2000 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 53

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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