Hi,

I have a database of Requestors and Request IDs. I used Vlookup to Return
the requestors name if i enter a request ID coz one request ID can be of one
requestor only.

But....One requestor can have many request IDs. Now what function should I
use to return all the request IDs if I enter one requestor's name.

For example ---

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

Now as per this sample, If i enter E456, VLOOKUP would return A. But If I
enter A, I want excel to return E456, E222 and D333.

Thanks in advance.
GARY

Hi!

This data is in the range A2:B6:

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

D2 = lookup value = A

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$6,D$2),INDEX(A$2:A$6 ,SMALL(IF(B$2:B$6=D$2,ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"Gary" wrote in message
...
Hi,

I have a database of Requestors and Request IDs. I used Vlookup to Return
the requestors name if i enter a request ID coz one request ID can be of
one requestor only.

But....One requestor can have many request IDs. Now what function should I
use to return all the request IDs if I enter one requestor's name.

For example ---

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

Now as per this sample, If i enter E456, VLOOKUP would return A. But If I
enter A, I want excel to return E456, E222 and D333.

Thanks in advance.
GARY

Hi Biff.

It returns the first one correct. but it gives me a #NUM error in all the
cells after that as I copy down.


"Biff" wrote in message
...
Hi!

This data is in the range A2:B6:

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

D2 = lookup value = A

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$6,D$2),INDEX(A$2:A$6 ,SMALL(IF(B$2:B$6=D$2,ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"Gary" wrote in message
...
Hi,

I have a database of Requestors and Request IDs. I used Vlookup to Return
the requestors name if i enter a request ID coz one request ID can be of
one requestor only.

But....One requestor can have many request IDs. Now what function should
I use to return all the request IDs if I enter one requestor's name.

For example ---

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

Now as per this sample, If i enter E456, VLOOKUP would return A. But If I
enter A, I want excel to return E456, E222 and D333.

Thanks in advance.
GARY

Also, if i enter G or B in D2, it still gives me E456.


"Biff" wrote in message
...
Hi!

This data is in the range A2:B6:

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

D2 = lookup value = A

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$6,D$2),INDEX(A$2:A$6 ,SMALL(IF(B$2:B$6=D$2,ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"Gary" wrote in message
...
Hi,

I have a database of Requestors and Request IDs. I used Vlookup to Return
the requestors name if i enter a request ID coz one request ID can be of
one requestor only.

But....One requestor can have many request IDs. Now what function should
I use to return all the request IDs if I enter one requestor's name.

For example ---

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

Now as per this sample, If i enter E456, VLOOKUP would return A. But If I
enter A, I want excel to return E456, E222 and D333.

Thanks in advance.
GARY

Tell me the EXACT location of you're data and post the EXACT formula you
used.

Biff

"Gary" wrote in message
...
Also, if i enter G or B in D2, it still gives me E456.


"Biff" wrote in message
...
Hi!

This data is in the range A2:B6:

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

D2 = lookup value = A

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$6,D$2),INDEX(A$2:A$6 ,SMALL(IF(B$2:B$6=D$2,ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"Gary" wrote in message
...
Hi,

I have a database of Requestors and Request IDs. I used Vlookup to
Return the requestors name if i enter a request ID coz one request ID
can be of one requestor only.

But....One requestor can have many request IDs. Now what function should
I use to return all the request IDs if I enter one requestor's name.

For example ---

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

Now as per this sample, If i enter E456, VLOOKUP would return A. But If
I enter A, I want excel to return E456, E222 and D333.

Thanks in advance.
GARY

I tried it with the same data, location and formula that you posted. dint
change anything.

"Biff" wrote in message
...
Tell me the EXACT location of you're data and post the EXACT formula you
used.

Biff

"Gary" wrote in message
...
Also, if i enter G or B in D2, it still gives me E456.


"Biff" wrote in message
...
Hi!

This data is in the range A2:B6:

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

D2 = lookup value = A

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$6,D$2),INDEX(A$2:A$6 ,SMALL(IF(B$2:B$6=D$2,ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"Gary" wrote in message
...
Hi,

I have a database of Requestors and Request IDs. I used Vlookup to
Return the requestors name if i enter a request ID coz one request ID
can be of one requestor only.

But....One requestor can have many request IDs. Now what function
should I use to return all the request IDs if I enter one requestor's
name.

For example ---

E456 --- A
E123 --- B
E222 --- A
F567 --- G
D333 --- A

Now as per this sample, If i enter E456, VLOOKUP would return A. But If
I enter A, I want excel to return E456, E222 and D333.

Thanks in advance.
GARY

"Gary" wrote:
I tried it with the same data, location and formula that you posted. dint
change anything.

But perhaps you overlooked Biff's step on array-entering the formula,
Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

But perhaps you overlooked Biff's step on array-entering the formula,
Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (CSE)

In the formula bar, if confirmed correctly you should see* curly braces { }
inserted by Excel at both ends: {<formula} (don't type-in these braces!).
Once confirmed correctly, the array formula can then be copied down as per
normal formulae.
*Visual check this before you proceed to copy the formula down

Note that the CSE has to be re-done each time should the formula be edited
for whatever reason. You'd get wrong results if the formulas are not
correctly array-entered. It's all too easy to forget to do the CSE <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Oh yes Max....i actually overlooked it.

Thanks...its working.

Thanks Biff.

"Max" wrote in message
...
But perhaps you overlooked Biff's step on array-entering the formula,
Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (CSE)

In the formula bar, if confirmed correctly you should see* curly braces
{ }
inserted by Excel at both ends: {<formula} (don't type-in these braces!).
Once confirmed correctly, the array formula can then be copied down as per
normal formulae.
*Visual check this before you proceed to copy the formula down

Note that the CSE has to be re-done each time should the formula be edited
for whatever reason. You'd get wrong results if the formulas are not
correctly array-entered. It's all too easy to forget to do the CSE <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad you got Biff's solution up and working !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary" wrote:
Oh yes Max....i actually overlooked it.
Thanks...its working.
Thanks Biff.

Glad you got Biff's solution up and working !

Me too!

Thanks to Max for the assist, also!

Biff

"Max" wrote in message
...
Glad you got Biff's solution up and working !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary" wrote:
Oh yes Max....i actually overlooked it.
Thanks...its working.
Thanks Biff.

Thanks a bunch to both of you. :)


"Biff" wrote in message
...
Glad you got Biff's solution up and working !

Me too!

Thanks to Max for the assist, also!

Biff

"Max" wrote in message
...
Glad you got Biff's solution up and working !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary" wrote:
Oh yes Max....i actually overlooked it.
Thanks...its working.
Thanks Biff.