Function Help: Data validation, nested if's, and lookup two table request

[Revelation343]

Table 1: (Data)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]Rank[/TD]
[TD]Trainings[/TD]
[TD]Presentations[/TD]
[TD]Total[/TD]
[TD]Requirements Met?[/TD]
[/TR]
[TR]
[TD]Rob[/TD]
[TD]Instuctor[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Teacher[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]27[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2: Requirements

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Trainings[/TD]
[TD]Presentations[/TD]
[TD]Total Points[/TD]
[/TR]
[TR]
[TD]Instructor[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Teacher[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Student Teacher[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Student Presenter[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Student Aide[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Intern[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Student[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Basic[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


Excel version: 2013 (Windows)

Background: I am looking for a quick way to make sure the criteria of my role-play group is met through Excel. I'm not exactly sure how to combine the necessary lookup/array/nested if functions to make it happen. I haven't done anything moderately challenging in Excel since 2007 and my brain is at a loss on this one.

Results I am Looking For: I want Table 1, Column 6 to use an excel formula to: Lookup the Rank of the user, then verify that all criteria has been met. If it has not been met, then I would like it to tell me which criteria has not been met. I would be content with it just saying "NO" but if it could say "Trainings not met" or "Presentations not met" or "Total Points not met" or if there is a function advanced enough to identify that two or more have not been met then "Trainings not met" AND/OR "Presentations not met" AND/OR "Total Points not met". If the criteria is met, then a simple "Yes" is all I need there.

This seems like a lot to me, I remember it being possible, I just don't remember how I did it before and I don't know that I had this many criteria to meet the last time I did it.

 

[CastroTheCat]

You could put all this in one function, but it would be a whopper - at least 3 nested IF statements, 6 or so VLOOKUPs...... ick.

I've written them, but I almost always regret doing so - they're really difficult to error check or alter later on, because they're so impossible to read.

It might be simpler to add some 2 columns to compare the requirements; use a VLOOKUP to pull out the actual requirements (call these columns G and H)

Once you've done that, then you could build a much simpler set of IF statements, eg:

=IF(AND(G2>C2,H2>D2),"Neither requirement met",IF(G2>C2,"Trainings not met",IF(H2>D2,"Presentations not met","All requirements met")))





"I'm not a genius. I'm a chicken"
Pinky Pie

 

[Revelation343]

I see what you mean, and maybe going with more columns and vlookups is a better option because the data table I pulled is two rows of about 50 and the point is for it to automatically check the rank. Total points needs to be validated as well.

What sort of VLOOKUP statements would I use to make the additional columns?

 

[Revelation343]

I apologize for the double post but I did not see an edit button. I figured the VLOOKUP out and used 3 columns to look up whether Trainings, Presentations, and Totals have been met.

I used this: =VLOOKUP(B2,$N$3:$Q$10,3,FALSE)

Considering the Three new columns:

[TABLE="width: 372"]
<tbody>[TR]
[TD]Presentations Met?[/TD]
[TD] Trainings Met?[/TD]
[TD]Total Met?[/TD]
[TD]Quota Met?[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there another nested IF you could provide with this validation?

I need the following results:

All requirements met
Need more presentations (If total points and trainings are met)
Need more trainings (If presentation and total points are met)
Need total points (If trainings and presentations are met)
Need trainings and total points (If only presentations are met)
Need presentations and total points (If only trainings are met)
No requirements met (If no requirements are met)


Seems like on heck if an IF statement to me to make, but um...maybe? And if not and we need more columns or what not I will put forth whatever effort I can to learn more.

In the meantime I am simply using conditional formatting for the Yes and No's which is easy enough to glance at and know who needs what, just want to see if I can clean it up further.

 

[CastroTheCat]

The problem is that nested IF's get more complicated when you have more than 2 variables. Again, you can do them, but they're very complex and difficult to trouble shoot.

Conditional Formatting is a good way to go, and Excel 2007 and after can sort/filter by colour.

If you want some text to filter/sort by, I'd suggest creating a second lookup table with the possible combinations on it:

Combo |Text
NoYesYes |Need more presentations
NoNoNo |No requirements met

etc etc


Then use a VLOOKUP with a concatenated text string as a search term to find the right text:

=VLOOKUP(G2&H2&I2,'Lookups'!$A$2:$B$8,2,0)



Hope that makes some sense.





"I'm not a genius. I'm a chicken"
Pinky Pie

 

[Revelation343]

The problem is that nested IF's get more complicated when you have more than 2 variables. Again, you can do them, but they're very complex and difficult to trouble shoot.

Conditional Formatting is a good way to go, and Excel 2007 and after can sort/filter by colour.

If you want some text to filter/sort by, I'd suggest creating a second lookup table with the possible combinations on it:

Combo |Text
NoYesYes |Need more presentations
NoNoNo |No requirements met

etc etc


Then use a VLOOKUP with a concatenated text string as a search term to find the right text:

=VLOOKUP(G2&H2&I2,'Lookups'!$A$2:$B$8,2,0)



Hope that makes some sense.





"I'm not a genius. I'm a chicken"
Pinky Pie

Never knew about the combos with the & & & inside the function. Very interesting. You have made my tool work 100% as described in the original post. Thank you very much for your help!