Lookup question

[A_SIRAT]

Please assist me solve this. I have explained in the spreadsheet.

[#2]

@A_SIRAT 

First remove alle empty rows in the column range H:K.

In the last part enter 0 for the lower value and 9999999 for upper value.

=LOOKUP(PI(),1/($H$2:$H$127=E2)/($I$2:$I$127<=B2)/($J$2:$J$127>=B2),$K$2:$K$127)

[#3]

@Detlef Lewin 

Thanks Detlef.

It has worked.

Would you mind to explain the formula. 

[#4]

@A_SIRAT 

Dettlef,

I have added a customer column.

The Lookup should also check the customer column and give the right %.

Please assist.

[#5]

@A_SIRAT 

1/($H$2:$H$127=E2)/($I$2:$I$127<=B2)/($J$2:$J$127>=B2)

1/(range1=criteria1)/(range2=critera2)/...

This calculates in a series of TRUEs (1) and FALSEs (0). By dividing 1/... you get a series of 1s and DIV/0! errors.

LOOKUP() has some special properties:

It ignores errors.

It works from botton to top/right to left.

If it does not find the search criteria (in this case PI()) it looks up the next smaller value which is (by ignoring errors) 1. With the position of this 1 LOOKUP() gets the value from the same position in the output vector.

Simple as that. :)