Data Index

[cwigelsw]

I have having trouble designing an index formula, I have the table and the data setup but cannot get the criteria correct. The biggest problem that I can see is that the lookup criteria for both columns and rows would be constantly changing.

I have tried inserting V and H look ups but keep getting a#Ref error

below is the formula I am trying. The data table is in B7 through S24 with column A and Row 6 being the headers that need to be the look up values.

=INDEX(Sheet2!$A$6:$S$24,(VLOOKUP(D5,Sheet2!$A$6:$A$24,1,TRUE)),(HLOOKUP(G5,Sheet2!$A$6:$S$6,1,TRUE)))

any help will be appreciated.

 

[scottylad2]

=INDEX(Sheet2!$A$6:$S$24,(VLOOKUP(D5,Sheet2!$A$6:$A$24,1,TRUE)),(HLOOKUP(G5,Sheet2!$A$6:$S$6,1,TRUE)))

any help will be appreciated.

Are you sure your understanding the Index formula? Your looking up a grid for the first argument

INDEX(Sheet2!$A$6:$S$24

and you'll be referencing a grid like this, you'rs will be bigger due to space constraints

Excel Workbook
	A	B	C	D	E	F	G	H
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sheet3

Your second argument will be to select a Row number, which can be done by either a hardcoded input or via a formula that will evaluate to a number which can then be referenced. It's quite often if your setting up like you have then you would the Match Function to find the Row reference and the Column Reference. In the sample, if you wanted to index to return how much a salesman returned on a certain day, you would use index to create a matrix of the cash returns like this then match to first select the ordinal Row position and similar to select the weekdays position also

Excel Workbook
	A	B	C	D	E	F	G	H
3		Salesman	Weekday	Returns
4		eve	Tuesday	376.00
5
6		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
7	Tom	635.00	174.00	434.00	706.00	93.00	684.00	722.00
8	sal	130.00	160.00	208.00	262.00	464.00	613.00	629.00
9	eve	666.00	376.00	663.00	499.00	624.00	718.00	292.00
10	matty	562.00	370.00	490.00	68.00	143.00	654.00	546.00
11	robert	329.00	81.00	479.00	108.00	530.00	490.00	387.00
12	callumn	635.00	611.00	348.00	242.00	233.00	392.00	727.00
13	eddie	626.00	239.00	604.00	332.00	107.00	146.00	645.00
14	marion	433.00	72.00	337.00	109.00	456.00	158.00	422.00
15	shirley	67.00	366.00	614.00	284.00	351.00	499.00	70.00
16	stewart	515.00	111.00	652.00	218.00	145.00	104.00	336.00
17	simmone	152.00	386.00	657.00	451.00	714.00	334.00	123.00
18	stephen	388.00	257.00	193.00	135.00	122.00	236.00	444.00
19	walter	605.00	460.00	122.00	701.00	352.00	475.00	743.00
20	wendy	103.00	288.00	203.00	482.00	285.00	577.00	748.00
21	siobahn	96.00	179.00	438.00	559.00	351.00	431.00	242.00
22	derek	359.00	558.00	133.00	286.00	92.00	643.00	648.00
23	pauline	651.00	621.00	79.00	428.00	433.00	287.00	229.00
24	willie	78.00	136.00	370.00	470.00	87.00	494.00	495.00
Sheet3