# Need help in putting a formula

#### Sudhama Naik

##### New Member
Need help in putting up a formula in the Cell highlighted in Yellow. The orange cells are manually entered and the ones is Gray is the lookup range. So when i enter the orange cells the Yellow cell with a formula should show the Percentile from the Gray table by looking up the salary entered in the Orange cell and comparing the Currency, Desig, Level and give the Percentile. I tried combination of Vlookup, Index and Match but unable to figure out.

#### Attachments

• 10.1 KB Views: 9

#### sgmpatnaik

##### Active Member
@Sudhama

Welcome to chandoo.org

please check this if it is help full

=IFERROR(VLOOKUP(\$G\$3&\$H\$3&\$I\$3&\$J\$3,\$A\$1:\$E\$22,5,TRUE),"")

Or

Array Formula using with Ctrl+Shift+Enter

=IFERROR(INDEX(\$E\$2:\$E\$22,MATCH(G3&H3&I3&J3,\$A\$2:\$A\$22&\$B\$2:\$B\$22&\$C\$2:\$C\$22&\$D\$2:\$D\$22,0)),"")

Thanks

Last edited:

#### Sudhama Naik

##### New Member
Hello Patnaik...thanks for the response, unfortunately both the formula is giving the correct result.

#### NARAYANK991

##### Excel Ninja
Hi Naik ,

Given the 4 inputs of Currency , Designation , Level and Salary , what is the proper logic to return the Percentile ?

In the example in your workbook , the combination of DD , Desk and Level does not exist for the input level of 24. Should only the entered salary be looked up ?

Narayan

#### Sudhama Naik

##### New Member
Hi Narayan. ..The salary could lie within the ranges in the grey table..so when ever I enter an amount it should see which percentile it falls under given the criteria of designation , currency and level. Even when I put a correct combination I am unable to arrive at a formula that looks up the salary within the range given in the table and matching the criteria that I input. I am getting close to using vlookup and match function but compares only one criteria. I am not getting the syntax to put multiple match criteria in a vlookup function.

#### Somendra Misra

##### Excel Ninja
Hi Naik,

Assuming in your table salary is in the ascending order as you have given and different other categories are in same order. You can try below array formula:

=LOOKUP(J3,OFFSET(D2,MATCH(G3&H3&I3,A2:A22&B2:B22&C2:C22,0)-1,0,COUNTIFS(A2:A22,G3,B2:B22,H3,C2:C22,I3)),OFFSET(E2,MATCH(G3&H3&I3,A2:A22&B2:B22&C2:C22,0)-1,0,COUNTIFS(A2:A22,G3,B2:B22,H3,C2:C22,I3)))

Enter With Ctrl + Shift + Enter.

But I have one question? What if Cur - DD ; Desig - Supervisor ; Level - 2 and salary input 3,00,000 ? Than what will be the result as the above formula will give you an error in that case.

@sgmpatnaik

I think you should omit 0 as the third argument in the MATCH function in your second formula; than both your formulas are giving same result.

Regards,

#### sgmpatnaik

##### Active Member
@Some

I didn't remove 0 in my formula please check this

=IFERROR(INDEX(\$E\$2:\$E\$22,MATCH(G3&H3&I3&J3,\$A\$2:\$A\$22&\$B\$2:\$B\$22&\$C\$2:\$C\$22&\$D\$2:\$D\$22,0)),"")

Thanks

Last edited:

#### Sudhama Naik

##### New Member
sorry gentlemen none of the formulas are working

#### Somendra Misra

##### Excel Ninja
sorry gentlemen none of the formulas are working
Hi Naik,

Can you explain further how it did not worked? Means does it not show correct value OR does it throws any error OR does it leaves the cells blank?

Else at least explain the logic with some manual output to explain the logic of your working so that suitable formula can be derived at.

Simply saying without explaining anything will not fetch you any result.

Regards,

#### Sudhama Naik

##### New Member
Hi Som ...Please find attached file and i have manully updated the information i requre in the cells highlighted in Yellow. The cells in organge is something that i will update manually and when done it should look up the table in grey and retrieve the P Values. May be you can help me now.

#### Attachments

• 12.1 KB Views: 3

#### Somendra Misra

##### Excel Ninja
@Sudhama Naik

See the file.

There are some issue. See Green Cell it is not in order?

Also see some more issue with your result and formula result.

Regards,

#### Attachments

• 12.9 KB Views: 2

#### Sudhama Naik

##### New Member
Hi Som ...looks like we are getting close, look at the line
Rs Manager 1 400,000 P90
it shows as P90 where as it should be P65.

#### Sudhama Naik

##### New Member
Never Mind Som ..pls ignore the previous msg ....you are awesome

#### Somendra Misra

##### Excel Ninja
Great you got it for the case you mentioned. See the file which will also consider the case when the salary will be lower than the lowest value in the band and the earlier formula was giving error.

Regards,

#### Attachments

• 13 KB Views: 2

#### Somendra Misra

##### Excel Ninja
Another option, a smaller version using one construct of old formula in K3 and copy down:

=IFERROR(VLOOKUP(J3,OFFSET(\$D\$2,MATCH(G3&H3&I3,\$A\$2:\$A\$22&\$B\$2:\$B\$22&\$C\$2:\$C\$22,0)-1,0,COUNTIFS(\$A\$2:\$A\$22,G3,\$B\$2:\$B\$22,H3,\$C\$2:\$C\$22,I3),2),2),INDEX(OFFSET(\$E\$2,MATCH(G3&H3&I3,\$A\$2:\$A\$22&\$B\$2:\$B\$22&\$C\$2:\$C\$22,0)-1,0,COUNTIFS(\$A\$2:\$A\$22,G3,\$B\$2:\$B\$22,H3,\$C\$2:\$C\$22,I3),1),1))

Regards,

#### Lakshmi Narain

##### Member
sorry gentlemen none of the formulas are working
Hope you got solution by somendra. for my knowledge
Currency, Desig, Level are constant.

Rs Clerk 32
DD Desk 105
Rs manager 1
DD Supervisor 2

#### Attachments

• 26 KB Views: 4

#### Sudhama Naik

##### New Member
Thank you Lakshmi

#### Lakshmi Narain

##### Member
Thank you Lakshmi
Please Check 2nd sheet. If you have changing the value targets you change in 2nd sheet