IF function when there is 12 possible outcomes

Copper Contributor

Hi any help gratefully received.

 

I have a sheet that looks up data and returns either 3A 3B 3C or 3D in one cell and 1, 2 or 3 in the adjacent cell. So two cells side by side.

 

In a third column I then need that cell to return the appropriate statement that I've entered (in a different sheet within the same workbook) based upon whatever combination of these two things there are. So 12 possible combinations?

 

So I want to know how to write a formula which will return a outcome for each of the possible outcomes? 

 

Thanks in advance.

3 Replies

@Chestnut502 , that could be like

=INDEX(<statements range>,
  MATCH(1,INDEX(
         (<ID1 of statements range>=<cell1>)*
         (<ID2 of statements range>=<cell2>),0) , 0)
)

 

Thank you @Sergei Baklan really appreciate you taking the time to help me out.

 

Would you mind taking a look at what I have?

 

I have tried what you suggested but I don't actually work with excel on a day to day basis so I'm struggling.

 

In the situation below I would need it to see John is 3D and group 2 and consequently want it to allocate the correct colour to his name. 

 

Thanks again,

 

Spreadsheet.bmp

@Chestnut502 ,

 

For this sample

image.png

the formula is

=INDEX($D$4:$D$15,MATCH(1,INDEX(($B$4:$B$15=$K4)*($C$4:$C$15=$L4),0),0))