Hi guys,
I'm trying to set a formula to pull values based on if they match a certain date (month). So there needs to be some sort of lookup or IF function I think, but I can't get my head around it.
In the attached sheet cell C1:E1 are variable dates. Cells C2:E5 should pull values from the table below depending on if the dates in C1:E1 match.
Any ideas?
Hi D-smoke
Try this?:
Copy down and across to populate 9 cells. Only problem is, the text isn't an exact match. I think it's to do with how you are populating the dates in C1:E1. The formula works if you copy the text from one of the months into the respective box in C1:E1.
Please Login or Register to view this content.
hi D-smoke. try this array formula in C2:
=INDEX($C$10:$Z$13,ROWS(C$2:C2),MATCH(TEXT(C$1,"mmyy"),TEXT($C$9:$Z$9,"mmyy"),0))
you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
One way ...
Put this in C2, normal ENTER to confirm will do:
=INDEX($C10:$Z10,MATCH(TRUE,INDEX(TEXT(C$1,"mmmyy")=TEXT($C$9:$Z$9,"mmmyy"),),0))
Copy C2 across to E2, fill down
TEXT is useful when it comes to "normalizing" dates to a particular month/year for matching purposes
------------------
Success? Celebrate it, hit the little star at the bottom left of my responses
Last edited by Max, Singapore; 01-11-2013 at 10:22 AM.
Max, Singapore,
I used your formula and it worked fine. Thank you very much for your help!
(Couldn't get the other formulas to work)
Cheers!
Great to hear, thanks for taking a moment to feedback. cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks