Lookup data formula

[susumalc]

Hi All,
I'm currently trying to put together a record of who takes out store keys for a student union, i'm trying to put together a spreadsheet that will tell me which stores a person is eligible for when i type in their student ID number.question.jpg

What i'd like is;
To type the id number into a cell in column A
Then if the number appears in column Z i'd like it to return the value on the same row from column AB or leave the field blank if it isnt in column Z.
I was then going to tweak it to so that each store column searches the relevant cells in the ID column (e.g. The Archery column (G) only searches id's from rows 2:15, Balroom (H) searches rows 23:47, etc)

I've tried lookup formulas people have suggested but so far have had no luck and really dont know where im going wrong. If anyone can help it'd be massively appreciated.
Thanks
Scott.

[Fotis1991]

Will be more useful if you upload a small sample workbook. Not a picture.

[Richard Buttrey]

Hi,

Re the first Q. Try

Please Login or Register  to view this content.

Where A1 contains your ID#

Re the 2nd Q. It really depends whether the ID number is unique in Column G, Column H etc. If so an adaptation of the above should do. If not upload your workbook so that we can see the request in context.

Have you considered a Pivot Table which may be able to help depending on your data layout?

[susumalc]

https://www.dropbox.com/s/iwy7gq2d81...tion%29.xlsm?m

Hi Both,
Thanks for the help with this i really appreciate it. I tried the suggested formula and it looks really promising; i've put the link to the document above i hope this is ok. Scott.

[Fotis1991]

Try in W2 and copy down.

=IF(ISERROR(INDEX($AB$2:$AB$10000,MATCH(A2,$Z$2:$Z$10000,0))),"",(INDEX($AB$2:$AB$10000,MATCH(A2,$Z$2:$Z$10000,0))))

[susumalc]

Hi Fotis,
THANK YOU SO MUCH. have tweaked it to all the clubs, works perfectly; Hero!!!
Scott.

[Fotis1991]

..................................