Hello!
I have 4 different terms called sessions at the college. I have a table listing all the sessions and the corresponding dates. I was trying to figure out how to compare the dates in I2:J2 against the table. Once it finds a match then it will return the session number that matches the date into column k. I've tried INDEX/MATCH but I just get a VALUE error.
I've uploaded a sample sheet. Any ideas? Thanks so much-this is killing my brain!
Hello,
You can try this array formule (Use [Shift]+[Ctrl]+[Enter])
Formula:
Please Login or Register to view this content.
Hoop this helps
WouterM
The Netherlands
Try pasting this into K2
=INDEX(G2:G5,SUMPRODUCT((I2=Table3103[Standard Start Dates])*(J2=Table3103[Standard End Dates])*ROW(Table3103[S'#]))-1)
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
oops, try this instead:
=INDEX($G$2:$G$5,SUMPRODUCT((I2=Table3103[Standard Start Dates])*(J2=Table3103[Standard End Dates])*ROW(Table3103[S'#]))-1)
I forgot to lock the references to $G$2:$G$5
Hi WouterM,
That worked beautifully! Thanks so much! Arrays are so amazing. Cheers!
Hi loginjmor,
Thanks so much for your awesome formula creation! This worked perfectly! I am so amazed how many different approaches can solve a problem. I never considered the difference between array/non array formulas...now I have homework!
Thanks again for your help!
Glad we could help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks