Match or nested if or vlookup?

[PKBrahma]

Sir,
I have my present salary in Col-B and new (fitment range) salary in Col-C. If Col-B matches with any one of the fitment range cells of Col-C, new salary will be the present salary. Else, my new salary will be fit in the next higher cell of the fitment range. I want to show my new Salary in Co-D. How can I do? Should I use nestedif or vlookup or match? Please guide me with code. Thanks & regards.

<colgroup><col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> </colgroup><tbody>
[TD="class: xl69, width: 64"]A[/TD]
[TD="class: xl69, width: 64"]B[/TD]
[TD="class: xl69, width: 68"]C[/TD]
[TD="class: xl69, width: 77"]D[/TD]

[TD="class: xl65"] Names
[/TD]
[TD="class: xl65"] Salary [/TD]
[TD="class: xl66, width: 68"] Fitment Range [/TD]
[TD="class: xl65"] New Salary
[/TD]

[TD="class: xl68"]AAA[/TD]
[TD="class: xl68"]78976[/TD]
[TD="class: xl68"]69700[/TD]
[TD="class: xl67"]
[/TD]

[TD="class: xl68"]BBB[/TD]
[TD="class: xl68"]82626[/TD]
[TD="class: xl68"]71800[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]CCC[/TD]
[TD="class: xl68"]83114[/TD]
[TD="class: xl68"]74000[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]DDD[/TD]
[TD="class: xl68"]84759[/TD]
[TD="class: xl68"]76200[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]EEE[/TD]
[TD="class: xl68"]84964[/TD]
[TD="class: xl68"]78500[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]FFF[/TD]
[TD="class: xl68"]86789[/TD]
[TD="class: xl68"]80900[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]GGG[/TD]
[TD="class: xl68"]86789[/TD]
[TD="class: xl68"]83300[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]HHH[/TD]
[TD="class: xl68"]86789[/TD]
[TD="class: xl68"]85800[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]III[/TD]
[TD="class: xl68"]86866[/TD]
[TD="class: xl68"]88400[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"]JJJ[/TD]
[TD="class: xl68"]88400[/TD]
[TD="class: xl68"]91100[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"]93800[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"]96600[/TD]
[TD="class: xl67"] [/TD]

</tbody>

 

[snjpverma]

can you provide an illustration example for the data you gave above? It will help us to understand it better.

 

[PKBrahma]

Sir,
The present salary will be fit in a new salary range which is given in col-c. If present salary is 78976, that will be fit in 80900. if present salary is 82626, that will be fit in 83300. if present salary is 84964, that will be fit in 85800. But, if the present salary is 88400, then the new salary will be 88400. What function will I use to get the new salary of the staff(names in Col-A) in Col-D? Thanks.

 

[PKBrahma]

Sir,
I have given illustration about what I want. Please advise me what to do. The value of a cell in col-B should be the next value in the cell of col-D. But, if the value of a cell in Col-B matches the value of a cell in col-D, then that value should not change to next value. Please advise me. Thank you.

 

[Nishant94]

Is this what you are looking for?

Unknown
	A	B	C	D
1	Names	Salary	Fitment Range	New Salary
2	AAA	78976	69700	80900
3	BBB	82626	71800	83300
4	CCC	83114	74000	83300
5	DDD	84759	76200	85800
6	EEE	84964	78500	85800
7	FFF	86789	80900	88400
8	GGG	86789	83300	88400
9	HHH	86789	85800	88400
10	III	86866	88400	88400
11	JJJ	88400	91100	88400
12			93800
13			96600
Sheet16

Cell Formulas
Range	Formula
D2	=INDEX($C$2:$C$13,IFNA(MATCH(B2,$C$2:$C$13,0),MATCH(B2,$C$2:$C$13,1)+1))

 

[Aladin Akyurek]

@​

PKBrahma

In D2 enter and copy down:

=IF(B2="","",INDEX($C$2:$C$13,MATCH(B2,$C$2:$C$13,1)+(LOOKUP(B2,$C$2:$C$13) < B2)))​

 

[PKBrahma]

Sirs,
Both the posts are very helpful. I owe my thanks and regards to both. I am successful. Let this post be closed.

 

[Aladin Akyurek]

You are welcome. Even if barely noticeable, 3-function solution should be a tad faster.

 

[PKBrahma]

Sir,
Even if the value of a cell in Col-B matches the value of a cell in col-D, then also the value should change to next value. In that case, what to change and where? Please advise. Thanks.

 

[Aladin Akyurek]

Sir,
Even if the value of a cell in Col-B matches the value of a cell in col-D, then also the value should change to next value. In that case, what to change and where? Please advise. Thanks.

In D2 enter and copy down:

=IF(B2="","",INDEX($C$2:$C$13,MATCH(B2,$C$2:$C$13,1)+(LOOKUP(B2,$C$2:$C$13) <= B2)))