• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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)),"")

other wise please inform

Thanks
 
Last edited:

NARAYANK991

Excel Ninja
Hi Naik ,

I think your problem description is inadequate.

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:

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

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.
 

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

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,
 
Top