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
19 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 49

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 49

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
 
LVL 49

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now