| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
something more than vlookup??
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
something more than vlookup??
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
something more than vlookup??
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
something more than vlookup??
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
something more than vlookup??
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
something more than vlookup??
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
something more than vlookup??
"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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup a Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| VLOOKUP Limitations | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
