CODEWITHSUNDEEP
excelexcel-formula
pmgibs
pmgibs

Reputation: 37

Return a value based of three different ranges

I'm trying to get Column H to populate with either SV, LV, or DS bases on a range of values in Columns C, D, E.

If C is between 270-300, and D is between 3-5, and E is between 900-2000, return a value of SV in column H.

If C is between 300-375, and D is between 3-5.2, and E is between 900-1700, return a value of LV in column H.

If C is between 375-420, and D is between 5.3-7.9, and E is between 900-1700, return a value of DS in column H.

I have tried nested IF AND statements, but I cannot seem to get it right. I'm ok doing this in VBA, if that would work better. Thanks for any help.

Upvotes: 0

Views: 952

Answers (3)

user18441193
user18441193

Reputation: 1

I've tried these formulas and they are returning invalid. I'm using two sheets and trying to reference data from both to return a value.

If "EID" on sheet 1 is found on column A of sheet 2; and If "column B of sheet 2) is = "1" Then return "date of completion" from sheet 2, otherwise return "not complete"

Upvotes: 0

ian0411
ian0411

Reputation: 4275

Here I have a more dynamic way to get the result you want using INDEX and SUMPRODUCT.

The formula I entered from cell H2 is:

=IFERROR(INDEX($P$2:$P$4,SUMPRODUCT(--(C2>=$J$2:$J$4)*--(C2<=$K$2:$K$4)*--(D2>=$L$2:$L$4)*--(D2<=$M$2:$M$4)*--(E2>=$N$2:$N$4)*--(E2<=$O$2:$O$4)*(ROW($P$2:$P$4)-1))),"")

What this does is to use SUMPRODUCT to match the criteria and then use INDEX to retrieve the corresponding outcome. You can try and see if this works for you.

Upvotes: 0

Kresimir L.
Kresimir L.

Reputation: 2441

You can also try this formula. 

=IF(AND(C1>=230,C1<300,D1>=3,D1<5,E1>=900,E1<2000),"SV",IF(AND(C1>=300,C1<375,D1>=3,D1<5.2,E1>=900,E1<1700),"LV",IF(AND(C1>=375,C1<420,D1>=5.3,D1<7.9,E1>=900,E1<1700),"DS","na")))

enter image description here

Upvotes: 1

Related Questions