Index and match in lookup table

[Samantha McNeill]

Hi,

I have tried numerous times to workout how I can return the required value from the attached workbook but just can't work it out. Think it is because the horizontal is tripping me up, so used to working with vertical ranges.

Basically, I need to look up the sales value between the lower and upper limited in row 6 and 7, then also look up the margin between lower and upper limit in row 9 and 10, then look for the Sales Person and Product in the matrix to return the value required. In the example I have highlighted this pink (ie 15).

Any help to solve this would be much appreciated.

Thanks in advance.

[jewelsharma]

this needs an array formula.

Please Login or Register  to view this content.

use ARRAY MATCH on Person & Product names to return the Row_Num to offset.
use ARRAY SUMPRODUCT to return the Column_Num that meets all limit criteria.

See attached.

Note: Array Formula must be entered/confirmed using [Ctrl] + [Alt] + [Enter]

Btw, your Margin criteria limits are named incorrectly. The lower limit should be called the upper limit and vice versa.
(-1 > -3)

HTH!

[xlnitwit]

Hi,

Are you sure you attached the correct file? I can't see any cells highlighted pink. Could you explain where you want the result and where the input for sales person and product is?

[Samantha McNeill]

Thanks jewelsharma, formula works perfectly.