HLOOKUP Question

[trainer75]

Hi,

Hope some one can assist me with this.

I am trying to do a HLOOKUP from data like below

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]13/14 P13W3[/TD]
[TD]13/14 P13W4[/TD]
[TD]14/15 P1W1[/TD]
[TD]14/15 P1W2[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]46[/TD]
[TD]55[/TD]
[TD]54[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Rolls[/TD]
[TD]28[/TD]
[TD]22[/TD]
[TD]42[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Butter[/TD]
[TD]66[/TD]
[TD]57[/TD]
[TD]58[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]14/15 P1W1[/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]Rolls[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]Butter[/TD]
[TD]58[/TD]
[/TR]
</tbody>[/TABLE]

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
13/14 P13W3	13/14 P13W4	14/15 P1W1	14/15 P1W2
Bread
Rolls
Butter
14/15 P1W1
Bread
Rolls
Butter

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]46[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]69[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA, align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #E2EFDA, align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=INDEX($B$2:$E$4,MATCH($A7,$A$2:$A$4,0),MATCH(B$6,$B$1:$E$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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