Look-up List?

[colmcg]

I have a sheet set up with the following;

Week 1 to Week 52 in Cells H1 to BG1
In cells H2 to BG21 data which is linked to a different workbook
Cell A24 contains a data validation list (H1 to BG1) i.e. Week 1 to Week 52

What I would like to do is select a week from the list in A24 (e.g. Week 4 which would be K1) then display the values from cells K2:K21 in cells F2:F21.

I have tried using Lookup but cannot figure out how to get it to work. I've done a search on this forum and there are lots of 'Lookup' posts but none do what I require.

Is it the Lookup function I should use or do I need to use a different function?

Your help would be gratefully appreciated.

 

[Krishnakumar]

Hi,

In F2 and copied down,

=INDEX($H$2:$BG$21,ROWS($A$2:$A2),$A$24)

HTH

 

[babycody]

Try vlookup

 

[colmcg]

Kris, babycody. Thanks for your replies.

Kris I pasted your formula in F2 and it returns #VALUE! I'm not sure what your formula does so I don't know how to edit it.

Babycody, I tried VLookup and also HLookup but cannot get it to work. Probably down to me not referencing the correct cells in the formula. can you advise what the formula should be please.

Thanks in advance.

Colin

 

[babycody]

A4 contains the the data validation drop down list.

Book2
	A	B	C	D	E
1	apple	1	2	3	4
2	pear	5	6	7	8
3
4	apple	1	2	3	4
5
Sheet1

 

[Krishnakumar]

Hi,

=INDEX($H$2:$BG$21,ROWS($A$2:$A2),MATCH($A$24,$H$1:$BG$1,0))

HTH

 

[colmcg]

Kris,

Thanks very much, works perfectly.