• 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

S

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

  • test.xlsx
    10.1 KB · Views: 9
@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:
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
 
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.
 
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,
 
@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 said:
sorry gentlemen none of the formulas are working :(
Click to expand...

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

  • test.xlsx
    12.1 KB · Views: 3
@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

  • test (10).xlsx
    12.9 KB · Views: 2
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.
 
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

  • test (10).xlsx
    13 KB · Views: 4
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,
 
You must log in or register to reply here.
Back
Top