General

Ask Experts & Get Answers to Your Questions - ASAP

Ask an Expert,
Get an Answer ASAP!

General

IN EXCEL 2010, I have a #N/A error with a formula as follows LOOKUP(V52,D5 D35,B5 B35)

Customer Question
IN EXCEL 2010, I have...
IN EXCEL 2010, I have a #N/A error with a formula as follows:
=LOOKUP(V52,D5:D35,B5:B35)

i WOULD LIKE TO SEND YOU THE EXCEL FILE FOR YOU TO CORRECT.

PLEASE REPLY!
Submitted: 6 years ago.Category: General
Show More
Show Less
Ask Your Own General Question
Answered in 45 minutes by:
4/20/2011
Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago
JamesStone
JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience: BA English Teaching, Data Analyst 7 Years
Verified
Are you able to post this in mediafire or something along those lines?
I'm fairly certain I can help if I have an understanding of what you are trying to do with it.
Ask Your Own General Question
Customer reply replied 6 years ago

IHave an excel file I would like to upload to you.

 

Please provide instructions for me to submit the excel file

Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago

It would probably be easiest to create a google shared doc account and make the spreadsheet public and let me take a look at it.


Otherwise you could try creating an account with mediafire, though the product is very spammy and not as reliable.

Ask Your Own General Question
Customer reply replied 6 years ago

 

James

OK!

http://www.2shared.com/document/IpRASc0Q/TEST_for_Just_Answer.html

 

The formula error is in Cell V47.

The formula should react the same as the formula in cell V48.

 

Please correct the formula in Cell v47.

 

Thanks as I wait for your answer,

Dan

Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago
I've got it open and am looking at it.

You're right, this is pretty strange, but I've got a good understanding of what is happening here.
Ask Your Own General Question
Customer reply replied 6 years ago
good, you hopefully can make it fool proof for me.
Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago

I went ahead and moved your sum columns over to the left side so that you can use a vlookup instead of just regular lookup. It is a bit more flexible in that I just reference the entire column range.


It appears the second formula refers to weekend values whereas the first formula refers to weekdays.

 

I've gotten it to work in the spreadsheet fine, but when I load it into google docs, there is an error about finding 9.99999900000

 

I noticed one of the items it references is a match formula with some real odd reference. What exactly is the purpose of that?

Also, what version do you use? I can save as xlsx or 97-2003 version

Ask Your Own General Question
Customer reply replied 6 years ago

You can send it in Excel 2010.

The layout can not change & I am not sure of the reference match you refer to.

Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago

I believe the conflict occurring is in cell v52, formula reads

 

=INDEX(D5:D35,MATCH(9.99999999999999E+307,D5:D35))

 

The index function typically wants to refer to a specific row/column

 

However, for the row parameter it has that odd MATCH function.


It would be nice to know what the purpose of that index formula is.


Do you basically just want to have that value be whatever the most current row/date is with a value in column D is? That would be my guess, without knowing for sure.

Ask Your Own General Question
Customer reply replied 6 years ago

Yes; I want the most current date in that collum.

 

So; maybe you can provide a more practicle formula for that cell.

Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago
Yes, the issue why one formula was working and the other is not is that the LOOKUP formula requires the values it is querying to be in acending order.

Since your weekend values are coincidentally in ascending order, it shows up no problem.

However, your weekday values are a whole other story.

Without being able to rearrange any of the columns, I'll have to see if I can get creative.
Ask Your Own General Question
Customer reply replied 6 years ago

 

Ultimately, cell V51 should be the most recent date corresponding to the row when a value is in either Colum D or E. In this case the answer in V51 should be Aptil 15, 2011.

 

If, however the last row containing values was row 14, the answer in V51 would be

April 11, 2011.

 

Therefore, the formulas in v47,v48, v52 & v53 become irrelevant.

 

Maybe this shows my inexperience with formulas.

Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago
At this point I've technically addressed your issue by replacing the formulas relating to getting your date and D column value.

However, I'm a bit concerned about the other one that was already "working" for the E column values.

Since column C appears to continually increase, I went ahead and used that as a stable reference point as opposed to the D column value.

If you anticipate the Sunday/Monday values will always continue to increase throughout the month, I don't think you'll have a problem.

If you think the Sunday/Monday E column values could fluctuate, I think it would be worth addressing now. I'd prefer to fix everything I can as opposed to just address the one issue.

Just let me know.
Ask Your Own General Question
Customer reply replied 6 years ago
Yes, treat collumn E the same as column D because it was coincidental the values were in ascending order.
Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago
OK, I got you all fixed up.

Let me know if you have any issues accessing this.

The bottom portion should always grab the most recent value for the D or E column range, while the date that is set to display is based on an *exact* match to the column value.

http://www.mediafire.com/?ejttkpoaslc2eva

Thanks.
JamesStone
JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience: BA English Teaching, Data Analyst 7 Years
Verified
JamesStone and 87 other General Specialists are ready to help you
Ask your own question now
Customer reply replied 6 years ago

Thank you for your help!

 

I tested the formula in another file which had the same value in two rows in the same column.

 

How would you modify the formula to identify the most recent date under those circumstances?

Expert: JamesStone, Analyst, Programmer, Writer replied 6 years ago
Since you are basically using the value as lookup, having two identical values in your range would make that method largely ineffective, so you'd be looking at an alternative identification method.

When I was first working on the problem, I was using Column C as the way to identify the most recent value, but that only worked for the D column since there were gaps in the Sunday/Monday.

Ask Your Own General Question
Ask JamesStone Your Own Question
JamesStone
JamesStone
JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
195 Satisfied Customers
Experience: BA English Teaching, Data Analyst 7 Years

JamesStone is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here.

DianeDallas, TX

< Previous | Next >

Meet the Experts:

Ellen

Ellen

Consultant

109 satisfied customers

attorney and legal researcher

P. Simmons

P. Simmons

Attorney

142 satisfied customers

45 years old, retired Marine Officer, current attorney

Kim D.

Kim D.

Consultant

34 satisfied customers

Researcher 25 yrs in small and large institutions

SuperiorConsultant

SuperiorConsultant

Internet Researcher

21 satisfied customers

BBA, serving as Marketing Executive and interested in tourism,sports, and anything and everything!

Cher

Cher

Educator-40+ yrs

1,684 satisfied customers

M.A., B.A., Author, Information & Research Specialist

Michael

Michael

Librarian

1,680 satisfied customers

20+ years as information professional

Martin

Martin

Engineer

876 satisfied customers

i'm 41 and i never stopped studying and experimenting

< Previous | Next >

Related General Questions
What are the chances of this happening again it's never
What are the chances of this happening again it's never happened before look at the image and what is the possibility or the odds of that happening again how about 15 minutes after that photograph was… read more
Michael
Michael
Librarian
Bachelor's Degree
1,680 satisfied customers
Could you do a research? I'd like to find LIPO SUCTION
For Sarah Teacher only: Hi Sarah, could you do a research? I'd like to find LIPO SUCTION surgeons in US that are specialized in ABS ETCHING, the more experienced the better… read more
Sarah
Sarah
Teacher
Master\u0027s Degree
2 satisfied customers
I need a professional group or tutor to help a teen ager
I need a professional group or tutor to help a teen ager learn to study and manage time---in the Cincinnati area. Do you know how I find this person? … read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
Does Leo guys connect closely and then ignore u totally over
Hi! Does Leo guys connect closely and then ignore u totally over another friend? … read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
I'm just looking for information about the different market
I'm just looking for information about the different market structures. What do I need to do to be able to access the information? … read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
RESEARCH Question: Can -Cola bottle caps with MyCokeRewards
RESEARCH Question: Can Coca-Cola bottle caps with MyCokeRewards codes / points be monetized in any fashion to obtain value from them now that the MyCokeRewards program was shut down by Coca-Cola last … read more
Michael
Michael
Librarian
Bachelor's Degree
1,680 satisfied customers
OnLy:. Could you please do another simple research?
This question is for ***** ***** OnLy: hi Mary . Could you please do another simple research?… read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
How do I call Uber, I am a senior and I need to have someone
How do I call Uber, I am a senior and I need to have someone to take me to Dr. Appointments and I live Oviedo Florida … read more
Michael
Michael
Librarian
Bachelor's Degree
1,680 satisfied customers
I am set for a pet scan tomorrow at 11:30 am. I was told not
I am set for a pet scan tomorrow at 11:30 am. I was told not to eat anything today except for drinking water or chicken broth or beef broth. There must be something I can eat. I have a headache and co… read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
I have BTV wall- safe E 911.bought in approx 2003 Safe is
I have BTV wall- safe E 911.bought in approx 2003 Safe is protected by number and key. unfortunately battery which is needed to type in code is empty. Cannot open safe. Could you be of help? Thanks an… read more
Schuyler
Schuyler
1,944 satisfied customers
Can someone explain what is trying to be said in this? “I
Can someone explain what is trying to be said in this? “I say, then, that if the temperate soul is good, one that is in the opposite state to this sensible one is bad; and that was the senseless and d… read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
We are at a bnb and I think it's a scam due to many hygienic
We are at a bnb and I think it's a scam due to many hygienic issues upon arrival. This house was purchased in 2015 according to my search. 3370 Kingsley Place Victoria BC Many lies and discrepancies… read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
Finishing my masters in Human factors. Have used up my 48
finishing my masters in Human factors. Have used up my 48 allotment for classes. Graduating with a Masters In Human Factor.s What will the government pay me to do my doctorate - like what kind of hous… read more
Mary Baker
Mary Baker
Owner
BA
38 satisfied customers
I am looking for someone who can help me find a Residential
I am looking for someone who can help me find a Residential Treatment Program for Reactive Attachment Disorder that takes Medicad - Preferrabley in Pennsyvania, Delaware, New Jersey area.… read more
Michael
Michael
Librarian
Bachelor's Degree
1,680 satisfied customers
Just one second. Marion Yu Porclain Girl /25088. Just 25088
Just one second JA: The Expert will know what to do. Please tell me everything you can so the Expert can help you best. Customer: Marion Yu Porclain Girl b/25088 JA: Is there anything else the Expert … read more
Nora
Nora
Accredited Appraiser & Antique Expert
Post-Graduate from Christies London
152 satisfied customers
Teacher only:. Could you please do a little simple research
This question is for Sarah, teacher only: hi Sarah. Could you please do a little simple research for me ?… read more
Sarah
Sarah
Teacher
Master\u0027s Degree
2 satisfied customers
We recently perchashed two Relief Wraps by using the
We recently perchashed two Relief Wraps by using the television advertisement. One wrap was fine, however, the other wrap had a hole in it and the little ball like thing were falling all over the plac… read more
Michael
Michael
Librarian
Bachelor's Degree
1,680 satisfied customers
What would be the best lab to get your drinking we'll water
What would be the best lab to get your drinking we'll water tested for contaminants , also does it help if you specifically mention a chemical your looking for or let them test for everything ? We are… read more
Michael
Michael
Librarian
Bachelor's Degree
1,680 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x