How JustAnswer Works:

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

Ask JamesStone Your Own Question

JamesStone
JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience:  BA English Teaching, Data Analyst 7 Years
20612472
Type Your Question Here...
JamesStone is online now
A new question is answered every 9 seconds

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 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: 3 years ago.
Category: General
Expert:  JamesStone replied 3 years ago.
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.
Customer: replied 3 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 replied 3 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.

Customer: replied 3 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 replied 3 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.
Customer: replied 3 years ago.
good, you hopefully can make it fool proof for me.
Expert:  JamesStone replied 3 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

Customer: replied 3 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 replied 3 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.

Customer: replied 3 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 replied 3 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.
Customer: replied 3 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 replied 3 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.
Customer: replied 3 years ago.
Yes, treat collumn E the same as column D because it was coincidental the values were in ascending order.
Expert:  JamesStone replied 3 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, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience: BA English Teaching, Data Analyst 7 Years
JamesStone and 81 other General Specialists are ready to help you
Customer: replied 3 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 replied 3 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.

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
< Last | Next >
  • 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!!!! Alex Los 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. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, 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. Esther Woodstock, 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. Robin Elkton, 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. Diane Dallas, TX
 
 
 

Meet The Experts:

 
 
 
  • Ron

    ASE Certified Technician

    Satisfied Customers:

    21579
    23 years with Ford specializing in drivability and electrical and AC. Ford certs and ASE Certs
< Last | Next >
  • http://ww2.justanswer.com/uploads/FO/fordguy4u/2011-12-17_222940_HPIM1257.64x64.JPG Ron's Avatar

    Ron

    ASE Certified Technician

    Satisfied Customers:

    21579
    23 years with Ford specializing in drivability and electrical and AC. Ford certs and ASE Certs
  • http://ww2.justanswer.com/uploads/lyeung1/2010-07-25_032152_tn_IMG_0241.JPG Dr. Y.'s Avatar

    Dr. Y.

    Urologist

    Satisfied Customers:

    18592
    I am fellowship trained specializing in general urology and reconstructive urology.
  • http://ww2.justanswer.com/uploads/docjohn174/2008-12-13_170143_johnask.jpg John's Avatar

    John

    Home Appliance Technician

    Satisfied Customers:

    13453
    Appliance repair business owner for over 43 years.
  • http://ww2.justanswer.com/uploads/MU/multistatelaw/2011-11-27_173951_Tinaglamourshotworkglow102011.64x64.jpg Tina's Avatar

    Tina

    Lawyer

    Satisfied Customers:

    8614
    JD, BBA Over 25 years legal and business experience.
  • http://ww2.justanswer.com/uploads/dermdoc19/2010-09-30_160749_Photo_122807_015.JPG dermdoc19's Avatar

    dermdoc19

    Dermatologist

    Satisfied Customers:

    3909
    30 years practice in general and cosmetic dermatology
  • http://ww2.justanswer.com/uploads/BI/birddoctor/2012-6-22_173214_birddoctor.64x64.png Dr. Pat's Avatar

    Dr. Pat

    Bird Veterinarian

    Satisfied Customers:

    3440
    25+ years working primarily or exclusively with birds
  • http://ww2.justanswer.com/uploads/RY/rydergar/2012-6-6_192240_IMG0328.64x64.JPG Dr. Gary's Avatar

    Dr. Gary

    Cat Veterinarian

    Satisfied Customers:

    3369
    DVM, Emergency Veterinarian, BS (Physiology)