# Derive one answer from 9 criteria and other variables

This is a follow on issue. In the attached Rows 4,9,14 contains titles for a set of calcs - upwind, downwind cliffs and escarpments, and downwind hills and ridges. Cells L5-L7 are the variables ( Note:-  I have disconnected the linked data and simply pasted some values in. Alternative values are shown in adjoining cells.

Cell L9 is the unique value i need to return.

Where the φ value <= 0.05 this is the same for the 3 titles - value 1.

In essence for upwind, there are 3 variables, for downwind, there are 6 variables based on 2 titles. Cell L9 needs to use cells L5-L7 data need to check against the 9 variables

I started with an if(and(.... statement and soon got myself stitched up. Any steers appreciated. Would i be better indexing?
CO-issue.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Here is a formula where the values from the 3 ranges are retrieved by indexing.

``````=IF(L6="Upwind",INDEX(G5:G7,IF(L7<0.05,1,IF(L7>0.3,3,2)),1),IF(L5="Escarpment",INDEX(G10:G12,IF(L7<0.05,1,IF(L7>0.3,3,2)),1),INDEX(G15:G17,IF(L7<0.05,1,IF(L7>0.3,3,2)),1)))
``````
CO-issue.xlsx

Experts Exchange Solution brought to you by