• 100% Satisfaction Guarantee

JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience:  BA English Teaching, Data Analyst 7 Years
20612472
JamesStone is online now

# 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.

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

James

OK!

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.

Dan

Expert:  JamesStone replied 5 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 5 years ago.
good, you hopefully can make it fool proof for me.
Expert:  JamesStone replied 5 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 5 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 5 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 5 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 5 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 5 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 5 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 5 years ago.
Yes, treat collumn E the same as column D because it was coincidental the values were in ascending order.
Expert:  JamesStone replied 5 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
Customer: replied 5 years ago.

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 5 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-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.
...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
< Previous | 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

• ### Dr. Y.

#### Satisfied Customers:

20083
I am fellowship trained specializing in general urology and reconstructive urology.
< Last | Next >

### Dr. Y.

#### Satisfied Customers:

20083
I am fellowship trained specializing in general urology and reconstructive urology.

### John

#### Satisfied Customers:

13453
Appliance repair business owner for over 43 years.

### Tina

#### Satisfied Customers:

8775
JD, BBA Over 25 years legal and business experience.

### GM Tech (Cam)

#### Satisfied Customers:

5780
GM Grand Master Technician 2007. 14 years experience.

### dermdoc19

#### Satisfied Customers:

4067
30 years practice in general and cosmetic dermatology

### Dr. Gary

#### Satisfied Customers:

3787
DVM, Emergency Veterinarian, BS (Physiology)