I need to lookup values under various conditions. For example, I have the following hard-coded data table in B30:E41 (data table doesn't post well...see attached):
1 78 667 1,912
1 12 746 1,930
1 110 766 1,971
2 64 1,020 2,505
2 36 1,051 2,598
I've written the following formula in an effort to return 1,971 from the 4th column:
{=+INDEX(E30:E41,MATCH(1,(B30:B41=1)*(C30:C41=MAX(C30:C41)),0))}
The problem is I need column B to be dynamic so that if I change B32 to "2", I would like the formula to return "1,912" as that corresponds to the maximum value in column C that is matched with a "1" from column B....
Any help would be appreciated!!
1 78 667 1,912
1 12 746 1,930
1 110 766 1,971
2 64 1,020 2,505
2 36 1,051 2,598
I've written the following formula in an effort to return 1,971 from the 4th column:
{=+INDEX(E30:E41,MATCH(1,(B30:B41=1)*(C30:C41=MAX(C30:C41)),0))}
The problem is I need column B to be dynamic so that if I change B32 to "2", I would like the formula to return "1,912" as that corresponds to the maximum value in column C that is matched with a "1" from column B....
Any help would be appreciated!!
