Formula Help

[vharp91]

Hello, I need help with a formula that will lookup vertically as well as horizontally to pull in a value into cell I70 on the first screenshot from what is entered on the spreadsheet in the second screenshot. I would like it to function so that when the value highlighted in cell I5 in the first screen shot is changed, it will update the value in cell I70 with the most recent data. In other words, when 7 is entered into cell I5 of the spreadsheet in the first screen shot, it will pull in 5,575 from cell K8 in the second screenshot into cell I70 in the first screenshot. When 8 is entered into cell I5 of the spreadsheet in the first screen shot, it will pull in 5,382 from cell L8 in the second screenshot into cell I70 into the spreadsheet in the first screenshot. So every time cell I5 in the first screenshot is updated, it will pull in the number that matches that department ID (column C in first screenshot and column B in second screenshot) as well as the matching pay period # in cell I5 in the first screenshot and row 5 of the second screenshot.

Screen shot 1:

Screen shot 2:

 

[Peter_SSs]

See if something like this is what you want. You will have to adjust the ranges to match your data.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

vharp91.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
5				1	2	3	4	5	6	7	8	9
6	Dept
7
8	105678			7048	8737	2956	3934	6039	6061	7346	8070	2069
9	33333			2010	1989	8133	6961	4083	1431	5021	4331	9726
10	987654			7659	2395	2811	9519	3601	9186	7921	2441	8154
Sheet2

vharp91.xlsm
	C						I
5							7
6	Dept
7
8	105678						7346
9	99999						#N/A
10	987654						7921
11	33333						5021
Sheet1

Cell Formulas
Range	Formula
I8:I11	I8	=INDEX(Sheet2!E$8:Y$1000,MATCH(C8,Sheet2!B$8:B$1000,0),I$5)