Multiple IF & AND Condition Requirement

Dear Forum Members.

Requesting your valuable input in the attachment. I have tried the various scenarios with multiple changes. But, not getting the right output i needed. Please correct me where i m making the mistake on this.

Question:

If employees existing sal in sheet 2 is less than and equal to slab in previous sheet (sheet 1) , then next immediate slab should be the revised pay in sheet 2 .

Example Scenario for Output

Existing Pay Revised Pay
10000 10211
10211 11200
11200 12300
11201 12300
12290 12300


Sample Attachment Enclosed for reference.

Thank you for the help.

Regards.
Thilak

Try this formula in E6:

=IFERROR(INDEX(Sheet1!$B$5:$H$5,MATCH(D6,Sheet1!$A$5:$G$5)+IF(COUNTIF(Sheet1!$A$5:$G$5,D6),0,1)),0)

Copy down as required.

Hope this helps.

Pete

Thank you sir. This works. I would like to request an small additional output.

1. If existing payscale is less than 10k slab, 10000 should be output.
2. If existing payscale is more than the final slab, Actual same existing salary should be output.

It would be really great if you can help me frame this in formula.

Thank You.

Use this in E6 instead:

=IF(D6 < Sheet1!$A$5,Sheet1!$A$5,IFERROR(INDEX(Sheet1!$B$5:$H$5,MATCH(D6,Sheet1!$A$5:$G$5)+IF(COUNTIF(Sheet1!$A$5:$G$5,D6),0,1)),MAX(D6,Sheet1!$H$5)))

then copy down.

Hope this helps.

Pete

Dear Sir.

This is really Brilliant. Thanks a million times to you for your time and knowledge sharing. It really helps.
I am an Intermediary user of Excel. Now learning this Complex mix of formulas.

Really this helps. I am marking the thread as Solved and Adding the Reputations.

Warm Regards.
Thilak