Looking up values using multiple criteria

[adrianplunkett]

Hello.

I have a tricky problem:

I need to return a value in a column in my spreadsheet, the value of which depends on multiple criteria.

My spreadsheet is very large, so I need to do this using a formula if possible.

I have attached a dummy spreadsheet to explain the problem.

I'd be grateful for any help! Please let me know if I need to clarify things further.

Many thanks,
A

[micope21]

Hi

Try this
Cell B2 =SUMPRODUCT((K$2:K$11<=C2)*(L$2:L$11>=C2)*J$2:J$11)

Cell A2 =INDEX($I$2:$I$11, MATCH(B2, INDEX($J$2:$J$11, 0), 0))

[adrianplunkett]

Perfect! Thanks.