Check Multiple conditions and arrive at result from another cell based on result

[Maatgie]

I am attaching the sample excel which contains 3 columns VT, CT and IT.

I need to check the value in VT columns and then the CT column, and if certain criteria is met with, the value in IT has to be returned.

For example, assuming values in VT2,CT2,IT2 are 0, VT56=0, CT56=21 and IT56=.75we need to check if VT value alongside CT value and then choose the corresponding IT value from the IT column. The VT and CT values are ranges, i.e., 0 VT - 14 CT days, 0% IT.

I need a formula to achieve this. I have tried the IF AND OR combination but not able to achieve this so far. Still trying at my end as well.

 

[PCosta87]

Hi,

Something like this?
See attached

Note that the formula must be completed with Ctrl+Shift+Enter as it is in array form.

 

[droopy]

I would take PCosta formula and change it to: =INDEX($C$2:$C$89,MATCH(F2&"-"&G2,$A$2:$A$89&"-"&$B$2:$B$89,0)), and still apply it as array formula (Ctrl-Shift-Enter).

Reason is because if you have, for example, 3 in VT and 21 in CT , and elsewhere 32 in VT and 1 in CT, both will match PCosta's formula, but it will return the first matching instance value. This second formula will help differentiate these 2 cases and return the right one.

Even better, you can make your range a Table (say it's called Table1), and use the table column names instead, so as to get rid of the range expanding size burden (when your range exceeds row 89).
Offset can be used as well, of course.

So formula with table would be as follows: =INDEX(Table1[IT],MATCH(F2&"-"&G2,Table1[VT]&"-"&Table1[CT],0))

Note that you should choose the separator string ("-" in my case) so that it cannot be confused with some string that could appear at the end of VT field or at the beginning of CT field. Otherwise you may face the same issue as I mentioned above.

 

[PCosta87]

I would take PCosta formula and change it to: =INDEX($C$2:$C$89,MATCH(F2&"-"&G2,$A$2:$A$89&"-"&$B$2:$B$89,0)), and still apply it as array formula (Ctrl-Shift-Enter).

Reason is because if you have, for example, 3 in VT and 21 in CT , and elsewhere 32 in VT and 1 in CT, both will match PCosta's formula, but it will return the first matching instance value. This second formula will help differentiate these 2 cases and return the right one.

Even better, you can make your range a Table (say it's called Table1), and use the table column names instead, so as to get rid of the range expanding size burden (when your range exceeds row 89).
Offset can be used as well, of course.

So formula with table would be as follows: =INDEX(Table1[IT],MATCH(F2&"-"&G2,Table1[VT]&"-"&Table1[CT],0))

Note that you should choose the separator string ("-" in my case) so that it cannot be confused with some string that could appear at the end of VT field or at the beginning of CT field. Otherwise you may face the same issue as I mentioned above.

Indeed... I didn't think of that but it makes perfect sense.
Using a delimiter like "-" is the way to go.

Thanks @droopy

 

[Maatgie]

Hi

I am working on the formula that you have provided. Let me get back shortly.

Thanks for the help.

Regards
Maatgie

 

[bosco_yip]

Another possible formula, and delimiter does not required

In H2, enter formula :

=SUMIFS(C:C,A:A,F2,B:B,G2)

Regards

 

[droopy]

There are pros and cons to each solution:
Index and Match solution is more flexible, because it will work regardless of the type of data in column C.
Sumifs will only work if column C has numeric values, but it does not need the CSE (ctrl+shift+enter) to make it an array formula, which is more convenient. So if only numeric values are considered in column C, it's fine.

There is as well a very elegant third way depicted here: http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

 

[Maatgie]

Friends,
thanks a lot for all your replies. I am still trying to adapt to the best one for my scenario. Will keep you posted once I am able to finalize based on my requirement

 

[Nebu]

Hi:

Here is a non-array formula if you are interested.

=INDEX(Table1[IT],MATCH(1,MMULT((F2=Table1[VT])*(G2=Table1[CT]),1),0))

Thanks

 

[droopy]

Hi:

Here is a non-array formula if you are interested.

=INDEX(Table1[IT],MATCH(1,MMULT((F2=Table1[VT])*(G2=Table1[CT]),1),0))

Thanks

Probably the best solution (unless someone else finds a flaw):
No array formula, no delimiter, no cell type dependency.