Find the year today subtract 1 then use that Year table for the lookup with month criteria.
To better understand i included a sample.
ef sample.xlsx
Find the year today subtract 1 then use that Year table for the lookup with month criteria.
To better understand i included a sample.
ef sample.xlsx
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Best I could do with 1 example of expected output vlady
Formula:Please Login or Register to view this content.
formula/result is in V10 (highlighted Blue )
Last edited by dredwolf; 03-12-2013 at 03:07 AM.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
hey vlady~ yeah, like dredwolf, i think you should include a little more examples. i reckon it's a little more complex like this when you reach future years.
=INDEX($G$9:$R$28,MATCH(YEAR(T10),INDEX(A9:E28,,MATCH(YEAR(TODAY())-1,$A$7:$E$7,0)),0),MONTH(T10))
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
In U10 cell
Formula:Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Oop... my previous post seems to be the same approach like benishiryo, but got beaten in the end usage of index function in which benishiryo avoided unnecessary usage of match function in the end with just a month function
One more suggestion quys...
=INDEX(G9:R28,MATCH(YEAR(T10),A9:A28,0),MATCH(MONTH(T10),G7:R7,0))
But why do i suspect..that you need something else?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
@Sixthsense:
a longer formula like yours to pick up the right month can cover more bases. i'm just lazy~
@ dredwolf & @ Fotis1991,
Just to let you know one condition is missing A7 to E7 (Search Value Current Year -1)
I'm gonna post this, just cause I worked on it after my first post
Formula:Please Login or Register to view this content.
I think with this I finally got the Initial problem (this year -1) covered
@ benishiryo,
But yours is neat and logically constructed based on the data structure which seems to be unchangeable (12 Months in a calendar year) so no need to worry about the comparing of each month with the data (which I did)
@ Sixthsense, yeah, thanks for the Heads Up, thats why I went and posted my (MUCH longer) solution, but I did work on it, so I thought it deserved posting ...lol (maybe a little conceited, but, it did work the brain a bit )
@ Sixthsense:
So my suspicion was correct ...
I just now drink the first cup of coffee...!
Thanks guys, I appreciate your time looking to this one.
Solutions offered are all excellent.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks