Solved

Excel formula to get value from the latest/highest day of a month returning errors

Posted on 2011-02-20
5
785 Views
Last Modified: 2012-05-11
In the sheet "monthly" of the attached file I have entered a formula (column b, yellow colourred) that should get the data from the sheet "wdme" at the intersection of the cell values of B1 and An.

An contains month-end-dates, but sheet "wdme" shows several working days of the respective month not necessarily including the last calendar day.

For any reason, my formulas returns either an #N/A error or 0.0%... probably a stupid first school year error again, but I have been unable to find out...

I would be grateful for an amended formula that avoids the error and just gathers the value from the latest/highest day / last that appears in row 1 of sheet wdme of the respective month.

Thanks.
0
Comment
Question by:BrdgBldr
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34938698
No attachment :)
0
 

Author Comment

by:BrdgBldr
ID: 34938861
Ok, Patrick, without attachment, 125 points is a bit meager... ;-)
ee14.xls
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 125 total points
ID: 34938895
Hello BrdgBldr,

You have the MATCH functions the wrong way round....and the wrong one has as 3rd argument too, try this formula in B2 copied down

=INDEX(wdme!$1:$65536,MATCH($A2,wdme!$A:$A,1),MATCH(monthly!$B$1,wdme!$1:$1,0))

regards, barry
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 125 total points
ID: 34938900
sorry, garbled my text...I meant to say that you also have the 3rd arguments wrong - the date look needs 1, the text lookup needs 0 for an exact match

barry
0
 

Author Closing Comment

by:BrdgBldr
ID: 34939009
thanks a lot Barry!
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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

920 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

14 Experts available now in Live!

Get 1:1 Help Now