Lookup / Match with multiple values.

[MikeJK]

Hello!

I'm creating a course schedule with all courses and their information.
I have a problem with automated "course started" formula.

When I insert the course name and the info when the course is taking place, I'd want that it looks from the table that has the course begun yet.

I want the formula to column C, and at columns E, F and G there's the values about which year, on what term and in what period the course is at.

For example:

Course	Started	Year	Term	Period
Programming 1	Yes	1	1	1
Mathematics 1	Yes	1	1	2
Programming 2	No	1	2	4
Networks 2	No	2	1	1

I've created to columns S-W another table with the beginning and ending times of all periods, (Beginning date, ending date, year, term, period) but I can't figure out how to get a formula which tests if the current period has started.

I know the formula at C has to be something like "look up if E,F,G's corresponding value at U,V,W is < TODAY()", but I can't write that as a formula.
So far I've tried with VLOOKUP, INDEX, MATCH, and combinations of those with IF's and everything but without any luck.


I hope someone can help me understand this.

Thanks in advance
-Mike

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[MikeJK]

Alright, here goes.

-Mike

[oeldere]

See if the result in row 3 is what you're trying to achieve.

Please reply.

[MikeJK]

Hello.

Thank you for the reply, and sorry it took so long for me to reply back.

It gave the right answer to the specific cell (3,3), but I can't implement that on the column C (3), and I don't even know if the answer was right just by coinsidence (50% change to guess anyway).
Also that sheet has strangely columns numbered instead of lettered, I think you can change that from the settings, but I'd like to get an solution I can implement elsewhere as well.

What I'm searching for, is an formula to column C's all values, formula which looks always the same row's values, and the same formula could be dragged to below.

In my example sheet, I'd want to C2 formula which is in english something like this: Look for match to E2, F2, G2 from U2:U15, V2:V15, W2:W15 and check if the match's beginning date at S2:S15 has been gone, if yes, then return "Yes", otherwise "No".
In C3: Look for match to E3, F3, G3 from U2:U15, V2:V15, W2:W15 and check if the match's beginning date at S2:S15 has been gone, if yes, then return "Yes", otherwise No etc etc.

-Mike

[oeldere]

try this one.

also in a better format (with letters instead of numbers).

[MikeJK]

It looks like it's close, but no, it doesn't work.

It doesn't change the value to "Course started" if I change the period (or year or term) to the row I'm testing.

Also I don't quite get why it's getting the value from the course's name.
You switched the Course's name and Course Started columns (B and C), but did you count the formula like it was other way around?

But I have a hunch that the answer is near.

-Mike

[oeldere]

Please Login or Register  to view this content.

You can move the formula to column C (and that works also).

But i don't get what you mean with the period.

The formula looks if the courses started before the date in a1 (= today)

So i can't help you any more.

[MikeJK]

Btw, just to add some details about the worksheet, B, C, E, F and G will have equal amount of inputs (For example the last ones would be B80, C80, E80, F80 and G80), and the number isn't known yet, but at columns S-W, the last one will be 15.

[BB1972]

Hi Mike,

I think I have what you want. I've used an extra column to the right of your table, to return the "START STATUS" of your modules/periods:

Please Login or Register  to view this content.

So IF the start date of a class is greater than today, OR the start date is blank, return "No" (the class has not started), otherwise return "Yes".

Using this as an index, you can use an INDEX/MATCH formula, which I have in Column D, as follows:

Please Login or Register  to view this content.

In the case of the line above, it says return from the INDEX ($X$2:$X$15) if a MATCH is found for E2 in the range $U$2:$U$15 AND if a MATCH is found for F2 in the range $V$2:$V$15 AND if a MATCH is found for G2 in the range $W$2:$W$15. (Note - this is an array formula and must be comitted with Ctrl, Shift & Enter simultaneously.) The formula is also wrapped in an IFERROR function, in case a Year/Term/Period combination is not found.

Is this along the lines of what you were trying for?

[MikeJK]

Thank you Brendan SO MUCH!!

I works perfectly as I quickly checked!

I was wondering though, can you include that column X's search in the formula so there would be as little extra info as possible?
I know the column can be hidden, but I always want to learn how to work with the formulas, and so far I've seen that if I get to include everything in the formula, it's easier to drag, move and implement in different situations.


I've also been thinking that same with a conditional formatting rule, as now I have to have 1 extra column as I don't know how to include the info to the formula.


------ OFFTOPIC, different problem.

If you're interested, here's the formula as is:

Sheet2!ColumnF: =IF(A2="";"";VLOOKUP(A2;Courses!$A$2:Courses!$C$100;3;FALSE))
And what it's supposed to do, is to check from Sheet1 if the course with the same code as the assignment has begun, and actually from the very same column this formula you gave me is.

Now I have this formula at separate column, returning Yes or No, and the conditional formatting colours the row simply based on the value, I'd want to put this whole formula to the formatting, but it doesn't work.




EDIT: I'll check this formula in the real sheet before marking as solved.

[MikeJK]

Yes, it works perfectly, thank you again!

I was thinking though that can you use test in index array.
I mean something like:

Please Login or Register  to view this content.

instead of:

Please Login or Register  to view this content.

and to X:

Please Login or Register  to view this content.

But I'll mark this solved now, as it's working like intended.

Thanks once more
-Mike

[BB1972]

Hi Mike,

Glad I could help.


As an aside, I visited Finland at Christmas 2010 for a few days - what a lovely country. My partner and I visited a fortress island called Suomenlinna, and it snowed heavily while we were there - not such a big deal for her, as she grew up in Austria, but I was as happy as the proverbial pig in mud

All the best.