VLOOKUP to return subsequent values on same criteria

[cbranfield]

Hi. Wondering if this is possible in Excel alone, without using Access
etc ; also, LOOKUP doesn't seem to be the function that will solve th
problem for me.

What I'm trying to do is the following:

Sheet 1: (DATA)

A B
1 PPE Vehicles
2 ABC XYZ
3 PPE Buildings
4 PPE Equipment
5 ABC RST
6 ....

Sheet 2: (RESULTS)

A
1 PPE
2
3
4

I want to lookup cell A1 on sheet 2, match it to rows 1,3,4 on sheet
and return the values in column B on sheet 1 to cells A2 - A4 on shee
2. Essentially, I'd like a VLOOKUP function to return more than on
value, or rather, subsequent values. How do I do that?

Can anybody help?
Many thanks.
Chri

 

[J.E. McGimpsey]

See the "Arbitrary lookups" section at

http://cpearson.com/excel/lookups.htm

 

[Don Guillett]

how about a macro
for each c in selection
if ucase(c)="PPE" then msgbox c.offset(,1)
next
or better yet use a find/find next. See FIND in vbe help.

 

[Alan Beban]

J.E. McGimpsey posted a link to formulas that will each return a
specified one of the multiple values. If the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook, the following, array entered in Cells A2:A4 of Sheet2, will
return a list of all of them:

=VLookups(A1,Sheet1!A1:B100,2)

Alan Beban

 

[cbranfield]

Hey guys. Thanks very much for the help.
It looks as though the function that'd suit my needs best would b
yours, Alan.

I can't get it to work as you described though... Was wondering what
missed - I've attached the test file.

Another thing, how do I make these functions available through Exce
without pasting the functions into each worksheet I create?
(Sort of like setting it to GLOBAL in VBA programming, I think.)

Thanks
Chri

+----------------------------------------------------------------
| Attachment filename: arraytester.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=357442
+----------------------------------------------------------------

 

[Alan Beban]

You need to download the file, give it a name and save it as an add-in.
Then in your workbook go to the VBE, click Tools|References and check
the add-in.

Alan Beban