HLOOKUP Question

[trainer75]

Hi,

Hope some one can assist me with this.

I am trying to do a HLOOKUP from data like below

	13/14 P13W3	13/14 P13W4	14/15 P1W1	14/15 P1W2
Bread	46	55	54	33
Rolls	28	22	42	12
Butter	66	57	58	69

<tbody>
</tbody>

On another tab I have a drop down box to select the period/week and I then want it to bring back the data related to the date and the item (like below)

	14/15 P1W1
Bread	54
Rolls	42
Butter	58

<tbody>
</tbody>

What I have as a formula but keep getting #Ref!

=IF(ISNA(VLOOKUP($B$1,INDIRECT($A1&"!A:K"),11,0)=TRUE),0,VLOOKUP($B$1,INDIRECT($A1&"!A:K"),11,0))

B1 is selecting the Date and the items are in column A from row 21 to 25

Hope this makes sense

Thanks in advance

 

[AlanY]

what about an index/match solution?

Excel 2012
	A	B	C	D	E
1		13/14 P13W3	13/14 P13W4	14/15 P1W1	14/15 P1W2
2	Bread	46	55	54	33
3	Rolls	28	22	42	12
4	Butter	66	57	58	69
5
6		14/15 P1W1
7	Bread	54
8	Rolls	42
9	Butter	58
Sheet1

Cell Formulas
Range	Formula
B7	=INDEX($B$2:$E$4,MATCH($A7,$A$2:$A$4,0),MATCH(B$6,$B$1:$E$1,0))

 

[trainer75]

what about an index/match solution?

Excel 2012

	A	B	C	D	E
1		13/14 P13W3	13/14 P13W4	14/15 P1W1	14/15 P1W2
2	Bread	46	55	54	33
3	Rolls	28	22	42	12
4	Butter	66	57	58	69
5
6		14/15 P1W1
7	Bread	54
8	Rolls	42
9	Butter	58

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B7 =INDEX($B$2:$E$4,MATCH($A7,$A$2:$A$4,0),MATCH(B$6,$B$1:$E$1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

That is superb,

Thank you very much for your quick reply.

I hadn't thought of index and match.

Thanks again

 

[AlanY]

you're welcome