Vlookup Won't Work For This

[Maureen5433]

I am hoping that someone can help - is there a formula (maybe similar to VLOOKUP) that will return more than just the first identical value in a list? I need the formula to keep looking and return all the matches in the list. I don't need totals, etc - I need the data in the column next to the value that corresponds to the cell being looked up. I am just learning macros and VBA and was hoping to avoid this simply because I am not that proficient at it yet and many people will be using this file once complete.

Sorry if this a repeat question. Thanks!

[ This Message was edited by: Maureen5433 on 2002-05-14 05:27 ]

[Aladin Akyurek]

On 2002-05-14 05:16, Maureen5433 wrote:
I am hoping that someone can help - is there a formula (maybe similar to VLOOKUP) that will return more than just the first identical value in a list? I need the formula to keep looking and return all the matches in the list. I don't need totals, etc - I need the data in the column next to the value that corresponds to the cell being looked up. I am just learning macros and VBA and was hoping to avoid this simply because I am not that proficient at it yet and many people will be using this file once complete.

Sorry if this a repeat question. Thanks!

[ This Message was edited by: Maureen5433 on 2002-05-14 05:27 ]

See my contribs in:

http://www.mrexcel.com/board/viewtop...c=7387&forum=2

http://www.mrexcel.com/board/viewtop...c=4365&forum=2

of which the following figure shows the layout.

Microsoft Excel - aaRetMultVals pleasehelpme.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	Field1
*	A	B	C	D	E
1	Field1	Field2	*	:alert('=MATCH(9.99999999999999E+307,Sheet1!A:A)-ROW(1:1)')>6	*
2	20	cat	*	39	:alert('=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")')>dog
3	39	dog	*	*	:alert('=IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)%3ECOUNTA($E$2:E2)),INDEX(OFFSET($B$2,MATCH(E2, OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E2,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")')>fox
4	40	cow	*	*	:alert('=IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)%3ECOUNTA($E$2:E3)),INDEX(OFFSET($B$2,MATCH(E3, OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E3,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")')>mouse
5	39	fox	*	*	:alert('=IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)%3ECOUNTA($E$2:E4)),INDEX(OFFSET($B$2,MATCH(E4, OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E4,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")')>*
6	50	rat	*	*	:alert('=IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)%3ECOUNTA($E$2:E5)),INDEX(OFFSET($B$2,MATCH(E5, OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E5,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")')>*
7	39	mouse	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin

[crimlet]

I have solved a similar problem by getting the maco to use the filter function. If the previous post does not work then I will post the code

[Aladin Akyurek]

On 2002-05-14 06:20, crimlet wrote:
I have solved a similar problem by getting the maco to use the filter function. If the previous post does not work then I will post the code

What do you mean by "If the previous post does not work"? If you really doubt, try to disprove that it doesn't work as advertised.

[crimlet]

On 2002-05-14 06:26, Aladin Akyurek wrote:

Sorry aladin, I did not meen to offend, but I had the greatest confidence in yourself, I was only say that if in the event that your solution did not give her the required results that there may be another alternative
What do you mean by "If the previous post does not work"? If you really doubt, try to disprove that it doesn't work as advertised.