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

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.
BrdgBldrAsked:
Who is Participating?
 
barry houdiniCommented:
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
 
Patrick MatthewsCommented:
No attachment :)
0
 
BrdgBldrAuthor Commented:
Ok, Patrick, without attachment, 125 points is a bit meager... ;-)
ee14.xls
0
 
barry houdiniCommented:
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
 
BrdgBldrAuthor Commented:
thanks a lot Barry!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.