If(INDEX(Match)),true,false) check emp table for values, then assign true or false

[e_var]

worksheet 'calculations', In column 'W' I want to check the table on WS 'training details' tbl 'emp_detail' for criteria (basic mod completed>0,LTV mod completed>0, Comped="") and they must be active - WS 'Details' tbl "Employees'.

I was using the Index(Match()) so the information/values in the 'calculations' WS would follow the 'emp_details' tbl since that is where the bulk of the info is from, and where the values would change. I know I could add another row to the 'emp_details' tbl and reference the data, but I am attempting to get away from that. I don't want to keep adding a column every time I need to display something.

The values from 'calculations' column 'W' would be used to pulled data onto the 'Follow up' WS. Part of the code

=IF([ID]>0,IF(Comped="",IF(Active="yes",IF(OR(Basics_Mod_Completed="",LTV_Mod_completed=""),TRUE,FALSE))))

I used in the 'Training Details' 'Solo f/u' column. I had attempted to simly use a IF(AND() statement but could not get it to work. So I was forced to use multiple IF statements.

Any help or insight greatly appreciated.

 

[Nebu]

Hi:

Is this what you are looking for?
Note: Formula in yellow cells.


Thanks

 

[e_var]

quick look, yes absolutely! Thanks!
I may need a decoder ring though...I will not have time to dig into it until later... Is the [#All] a regular built in function? I don't think I have seen it used before. I ran through a quick evaluation of the code, I don't think I would have come to that solution on my own.
I'll get into it later...
Thanks again

 

[Nebu]

Hi:
[#All] is because I am indexing it with a table.

Thanks

 

[Mike86]

@Nebu I played with this a bit yesterday and I have a similar question to e_var. The [#ALL] notation doesn't appear to be strictly necessary. For example, when I remove the [#ALL]s from your equation, the output doesn't change.

I did a quick Google and the [#ALL] notation appears to be trying to force a function to use all the data in the subsequently identified table columns. It seems like this should force the column into an array, which is fine. However, in my attempts to confirm this, Excel was quite happy to use the structurally referenced column as an array with or without the [#ALL] portion. I was unable to identify a function that didn't more-or-less understand what I wanted when structurally referencing a table column.

It would be very helpful if you could provide a specific example of where the [#ALL] notation is required for the desired effect.

 

[Nebu]

@Mike86

Its not a biggie, If you select the table column with headers it will automatically show #All in the formula, if you exclude the header from the selection it will not show #All. What I meant to say is that since the file posted by OP had tables the formula had #All, even if you select the range the formula should work, its just simple index formula inside an AND parameter.

Thanks