something more than vlookup??

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

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
Click to expand...

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
 
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.
 
Gary said:
I tried it with the same data, location and formula that you posted. dint
change anything.
Click to expand...

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


---
 
But perhaps you overlooked Biff's step on array-entering the formula, re:
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>

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

After requestor submits the form; Sent item of Requestor has got t 1
updates...preventing changes to previously entered values 8
Help Please creating Lookup/Filter Macro 1
Custom Message Form - Customization 1
Cannot access more than 250 items in a folder 15
Cannot access more than 250 items in a folder 3
Vlookup Function 3
INDIRECT(ADDRESS) inside a VLOOKUP 13

Back
Top