Complex If/Then Formula

[willhh3]

Hi All:

Need some help as this has my head spinning. Sample file attached with some notes on sheet 1. In a nutshell, I need to figure out the formula to say if a Code in Table 2 matches Table 1 (which can have duplicate values) and a second value of TYPE is Assigned or Additional, then return the highest value for a given month. There could be 3 possible values (0, .5 or 1).

I'm prob making it more complicated than it needs to be with nested if/thens. I also was tinkering with MAX. All to no avail. I'm going to keep trying but thought I'd reach out for assistance.

Thanks in advance for any guidance.

Whh3
Excel 2013

[FDibbins]

So where would this answer go, and could you give an example of what you expect?

[willhh3]

Thanks for the quick reply. In the sample workbook, Table 2 shows the expected values. In my real workbook, I would like to put the formulas in Table 2 b19:d26 in the example. Hope that makes sense.

I basically have an existing table that looks similar to Table 2 (a few more columns involved) and would like each code to populate for each month based on a separate sheet of raw data which is represented by Table 1.

Regards!

[FDibbins]

And the code could be repeated in Table 1 - and you want the highest value, correct?

[FDibbins]

If so, try this ARRAY formula in B19, copied across...
=MAX(IF($A$3:$A$14=$A19,C$3:C$14))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[willhh3]

Yes, I can’t think of any reason returning the highes value wouldn’t work. Yes, I’d repeat the formula in table one for each row with a unique Code value.

[FDibbins]

Did you try my formula?

[willhh3]

I won’t be able to give that a try till tomorrow, however I hope it was really as simple as you showed. I figured I was overly complicating it. Will give it a shot in the morning.

Do I have to use a range of cells or could I just use the column? Like [Code] or A:A? Over time the number of rows may increase. Thanks!

[FDibbins]

I added extra columns to compare against your sample anwers...

	A	B	C	D	E	F	G
17	Desired Outcome		Table 2
18	Code	Jan	Feb	Mar	Jan2	Feb3	Mar4
19	C10	1	0	1	1	0	1
20	C222	1	0.5	1	1	0.5	1
21	P135	0	0	1	0	0	1
22	Po900	1	0.5	0.5	1	0.5	0.5
23	T26	1	0	0	1	0	0
24	T321	1	1	1	1	1	1
25	T404	1	1	1	1	1	1
26	T404 a	0	0	1	0	0	1

E19 (which you would actually put in B10)
=MAX(IF($A$3:$A$14=$A19,C$3:C$14))
array entered
then copy across as needed

Yes, you could use full-column ref's but that could slow your file down.
Rather, extend the range to 2-3 times what you think you might need...
=MAX(IF($A$3:$A$10000=$A19,C$3:C$10000))

[willhh3]

FDibbins: I just tested it out on the test data and it indeed seems to do the trick! I think I was going down the wrong path with If/Then's when you hit the nail on the head. It really only needs to show the highest value for each match. I've never used max before and forgot about arrays. Thanks so much for your help! Marking this as "SOLVED."

[FDibbins]

Happy to help