Complex IF Statment

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi all...

I have a complex situation where I have 5 values listed in
seperate cells, that I want to compare to range of values.

example
my value
1-3-5-4-6

Compare values to
1-1-1-2-3
1-2-3-4-5
1-1-2-5-2
1-3-5-4-6

Next to each of these compared values I have the outcome
possibilities if all matches proerly
1-1-1-2-3 Yes-No-Maybe-BS
1-2-3-4-5 Yes-No-Maybe-Soo What-I'm Late
1-1-2-5-2 Yes-No-Maybe-I'm Late
1-3-5-4-6 Yes-No-Maybe-Soo What-BS

Now if "my values" are =to "compare values" display
the "outcome possibilities" in different cells like this.

"My Values" are =to "Compared Values" in row 566. Display
Yes in call a1 No in cell a2......

Does anyone have a idea of how I can make this work. Maybe
I need to use a different formula or think of it a
different way?

Thanks
Pete
 
Hi Pete
though i'm not so sure i understood you lets give it a try:
If your lookup value only exist once in your lookup range you can use
the following formula to get the row within that range:
=SUMPRODUCT(('compare'!A1:A20=A1)*('compare'!B1:B20=B1)*('compare'!C1:C
20=C1)*('compare'!D1:D20=D1)*('compare'!E1:E20=E1)*ROW('compare'!E1:E20
))

to get the corresponding values/text strings you could use INDEX: e.g.
=INDEX('compare'!F1:J20,SUMPRODUCT(.....),column_index)

HTH
Frank
 
Frank

This is not what I thought would work, but it goes to show
you a fresh look at a old problem, get you going again.
thanks for you insight on this, but I have one question.

On my formula below I have the 2 different formulas
entered in different cells, but when the second formula is
calculated I get 0's,2's and 3's along with my actual
results of yes-no-maybe. Any thoughts as to why this is
happening?

In cells C55-C62.
=SUMPRODUCT((Die1_Results=Die1)*(Die2_Results=Die2)*
(Die3_Results=Die3)*(Die4_Results=Die4)*(Die5_Results=Die5)
*ROW(Die5_Results))

In cells D55-D62.
=INDEX(Options,SUMPRODUCT(C55),1)
=INDEX(Options,SUMPRODUCT(C56),2)
=INDEX(Options,SUMPRODUCT(C57),3) and so on....

By the way my list of options is located in cells AK39-
AS7814.

Regards,
Pete
 
Hi Pete
first change
=INDEX(Options,SUMPRODUCT(C55),1)
to
=INDEX(Options,C55,1)
(no reason to use SUMPRODUCT

Do the cells in column C (C55...) return the correct results? That is
the correct row number. If yes, there may be a problem with your range
'Options'. If this does not start in row 1 you have to subtract
(starting row-1) fromt the value in C55 when using in the INDEX formula
Frank
 
Back
Top