Lookup based on Range

[genetiix]

I ran into this rather simple problem, however, when tackling it -- seems more difficult to do.

I have a Region (G) and a Department (H) on 'FP&A Owner' sheet and have a look up table with ranges. Based on a region/range, I want it to go to the "Lookup Table" and retrieve FP&A Approver (F) into 'FP&A Owner' (I) column. I put in an example of how it should look if pulled correctly.

I feel like it's a combination of IF, AND, and Array, but can't seem to quite get it to work.

Hope the link works!

Thank you!!

https://1drv.ms/x/s!Au8NDxZs8eye0QjmptNoRl8m9jBT

 

[FDibbins]

I am not able (allowed) to go to file-hosting sites, so could you provide some sample data and expected outcome here please?

 

[genetiix]

I am not able (allowed) to go to file-hosting sites, so could you provide some sample data and expected outcome here please?

Thanks Ford, Apologies on my end. --!

Here is the example:

Ex: Region 100, Dept 150 - Should retrieve 'Jeff'
Ex: Region 608 Dept 150 - Should retrieve 'Gene'

I'm not sure what formulas to write which would retrieve the owner based on a range in a data table.

Data Table
[TABLE="width: 500"]
<tbody>[TR]
[TD](A) Region From[/TD]
[TD](B) Region To[/TD]
[TD](C) Dept From[/TD]
[TD](D) Dept To[/TD]
[TD](E) Owner[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]299[/TD]
[TD]100[/TD]
[TD]199[/TD]
[TD]Jeff[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]299[/TD]
[TD]200[/TD]
[TD]299[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]609[/TD]
[TD]200[/TD]
[TD]500[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]999[/TD]
[TD]100[/TD]
[TD]199[/TD]
[TD]Gene[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Will there only ever be 2 departments to select from for each Region? If so, could they be put into separate columns?

 

[genetiix]

Will there only ever be 2 departments to select from for each Region? If so, could they be put into separate columns?

There will never be 2 departments per row. The summary table will look like so. The owner is the one I'm trying to write a formula to populate.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Dept[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]150[/TD]
[TD]Jeff[/TD]
[/TR]
[TR]
[TD]608[/TD]
[TD]150[/TD]
[TD]Gene[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

There will never be 2 departments per row. The summary table will look like so. The owner is the one I'm trying to write a formula to populate.

that wasn't what I asked, I asked if there will only ever be 2 departments for each region?