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
48 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 52

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 52

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 52

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 52

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 52

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

Industry Leaders: 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!

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…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

739 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