Eliminated Helper Row

[ron.bo]

I am trying to get a formula that will eliminate the helper row. Each week I need how much salaries increased over last week. I have the Id, Position and Salary. It is easy with the helper column "idposition" column 3. I would like to get a formula that would by bass the helper column. Right now the formula I have in column K is =VLOOKUP(I7,I:J,2,0)-VLOOKUP(I7,D:E,2,0).

Thanks for any help
Ronbo

PS. I can not just sum salaries and subtract.

B C D E F G H I J K

B C D E F G H I J K

 

[awoohaw]

try this:

mr excel questions 13.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2	Week 1					Week 2
3	id	position	idposition	salary		id
4	1	a		60000		1	a	60000		0
5	2	b		60100		2	b	60100		0
6	3	c		60200		3	c	60255		55
7	4	d		60300		4	d	60300		0
8	5	e		60400		5	e	60400		0
9	6	f		60500		6	f	60500		0
10	7	d		60600		7	d	60600		0
11	8	c		60700		8	c	60755		55
12
Sheet4

Cell Formulas
Range	Formula
K4:K11	K4	=I4-INDEX($E$4:$E$11,MATCH(G4&H4,$B$4:$B$11&$C$4:$C$11,0))

 

[ron.bo]

Thanks for your help. I have not been on the board for sometime, so I will get the add-in. I apologize for the poor post. You have perfectly reconstructed my data and your formula is perfect... but it gives me a #VALUE error. In thoughts on why I am getting this error.

Thanks for your patience and help.
Ron

 

[ron.bo]

Book1 (version 1).xlsb
	B	C	D	E	F	G	H	I	J	K	L
1	Week 1					Week 2
2	id	position	idposition	salary		id	position	idposition	salary
3
4	1	a	1a	60000		1	a	1a	60000	#VALUE!
5	2	b	2b	60100		2	b	2b	60100	0
6	3	c	3c	60200		3	c	3c	60255	55
7	4	d	4d	60300		4	d	4d	60300	0
8	5	e	5e	60400		5	e	5e	60400	0
9	6	f	6f	60500		6	f	6f	60500	0
10	7	d	7d	60600		7	d	7d	60600	0
Sheet1

Cell Formulas
Range	Formula
D2,I2:I10,D4:D10	D2	=+B2&C2
E5:E10	E5	=+E4+100
K4	K4	=I4-INDEX($E$4:$E$11,MATCH(G4&H4,$B$4:$B$11&$C$4:$C$11,0))
K5:K10	K5	=VLOOKUP(I5,I:J,2,0)-VLOOKUP(I5,D:E,2,0)

 

[ron.bo]

The problem is that I have one more column than you. My week two salary is in column J yours in I. So that is why it is not working. I changed it to this =J4-INDEX($E$4:$E$11,MATCH(G4&H4,$B$4:$B$11&$C$4:$C$11,0)) and it sure looks like it should work but I still get the #VALUE error.

Thanks,
Ron

 

[ExceLoki]

either K or L should work for you:

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	Week 1					Week 2
2	id	position	idposition	salary		id	position	idposition	salary
3
4	1	a	1a	60000		1	a	1a	60000	0	0
5	2	b	2b	60100		2	b	2b	60100	0	0
6	3	c	3c	60200		3	c	3c	60255	55	55
7	4	d	4d	60300		4	d	4d	60300	0	0
8	5	e	5e	60400		5	e	5e	60400	0	0
9	6	f	6f	60500		6	f	6f	60500	0	0
10	7	d	7d	60600		7	d	7d	60600	0	0
11	8	c	8c	60700		8	c	8c	60755	55	55
Sheet2

Cell Formulas
Range	Formula
D2,I2:I11,D4:D11	D2	=+B2&C2
K4:K10	K4	=J4-INDEX($E$4:$E$10,MATCH(G4&H4,$B$4:$B$10&$C$4:$C$10,0))
L4:L11	L4	=J4-INDEX(E:E,MATCH(G4&H4,B:B&C:C,0))
K11	K11	=J11-INDEX($E$4:$E$11,MATCH(G11&H11,$B$4:$B$11&$C$4:$C$11,0))
E5:E10	E5	=+E4+100

 

[awoohaw]

Thanks for your help. I have not been on the board for sometime, so I will get the add-in. I apologize for the poor post. You have perfectly reconstructed my data and your formula is perfect... but it gives me a #VALUE error. In thoughts on why I am getting this error.

Thanks for your patience and help.
Ron

Sometimes text values get non printing characters when you paste from this webpage. You just need to clean up the data if you are using my copied data. If you use the formulas on the data already in your workbook they should work.

do some comparison and length statments on columns B,C,G,&H to make sure B = G, and C = H.

 

[ron.bo]

It is totally weird. It does not work for me in 2007, which has nothing to to with it. I found it works as an array.

Thanks,
ROn

 

[awoohaw]

by array, do you mean entering hte formula with CTRL-SHFT-ENTER ?

 

[awoohaw]

It is totally weird. It does not work for me in 2007, which has nothing to to with it. I found it works as an array.

Thanks,
ROn

please update your profile to show what version of excel you use. 2007 doesn't have many features of 2010 and up.