Greater than less than with matching variables and maybe vlookup?

[NUppal]

Hi there! I've been struggling with this formula for a couple of days now and I keep deleting it and starting over.

This is the chart on a separate tab named "Notice Weeks" that I need to lookup. I highlighted all the information and named it severance5

This is my first tab (CA Tracker) where I will be filling column CO with the formula, I have the same entity column, and work state column as in the 2nd tab with the chart (Notice Weeks).
I'm dealing with looking up information in a chart on another tab and having that input into the first tab.

example of 1 scenario:

I basically need to say if entity is (column A), and work state is (column AN) and the years of service (column CH) is greater than (column D in "Notice Weeks") and less than (Column E in "Notice Weeks"), the notice period will be (Column C in "Notice Weeks"). That will be filed into column CO.

Would someone be able to assist me with this? Tried googling/youtubing but it's been a fail.

 

[Fluff]

Hi & welcome to MrExcel.
Is the dat on the Notice weeks sheet a structured table? If so what is it called?

 

[NUppal]

Hi & welcome to MrExcel.
Is the dat on the Notice weeks sheet a structured table? If so what is it called?

Thank you! Yes it is. It is called severance5

 

[Fluff]

Ok, how about

=FILTER(severance5[Notice Period],(severance5[Entity]=A2)*(severance5[Work State]=AN2)*(severance5[Min Weeks]<=CH2)*(severance5[Max Weeks]>=CH2))

 

[NUppal]

Ok, how about

=FILTER(severance5[Notice Period],(severance5[Entity]=A2)*(severance5[Work State]=AN2)*(severance5[Min Weeks]<=CH2)*(severance5[Max Weeks]>=CH2))

I tried, but the error #CALC! shows up.

 

[Fluff]

In that case can you post the data from the severance table.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Fluff]

Looking at your image again are the values in col CH text or numbers? Judging by the warning triangle they are probably text.

 

[NUppal]

Looking at your image again are the values in col CH text or numbers? Judging by the warning triangle they are probably text.

They are numbers. I have a formula in for that column

 

[Fluff]

In that case please see post#6

 

[NUppal]

In that case please see post#6

i was able to resolve it using your filter formula, it was an error on my part. thank you so much!