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