Looking up multiple values

[Guest]

Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do this in Excel? Thanks

 

[Jason Morin]

Formulas were really meant to return one value. It IS
possible to link several formulas together in one cell to
return multiple values, but the formulas get long and
complex. I would suggest tinkering with AutoFilter (Data >
Filter > AutoFilter). Or, construct several formulas over
several cells.

HTH
Jason
Atlanta, GA

-----Original Message-----
Is there any way to lookup multiple answers to a lookup

formula. Normally, Excel will stop at the first match it
finds and return that as the answer when, in fact, there
may be more than one answer. I want to find the other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated

with it: 6000 and 6010. I want to do a lookup of Part
No. 123 and find ALL the work orders associated with it
(listed separately, of course). Is there any way to do
this in Excel? Thanks

 

[Max]

Agree with Jason that using Autofilter is easiest / best suited.

However, if for some reason, the data table needs to be, say
hidden away from sight / harm, but with user lookup/search
allowed on the table, give this set-up a try:

Assuming your data table is
in Sheet1, cols A and B,
data from row2 down, with:

col A = Part No.
col B = Work Order

In a new sheet, say, Sheet2
--------------------------------
Put the labels:
in A1 = Part No.
in B1 = Work Order

Cell A2 will be where the Part No. will be input

Put in C2:

=IF(OR(ISBLANK($A$2),ISERROR(OFFSET(Sheet1!$A$1,SEARCH("*"&TRIM($A$2)&"*",Sh
eet1!A2)+ROW()-2,1))),0,OFFSET(Sheet1!$A$1,SEARCH("*"&TRIM($A$2)&"*",Sheet1!
A2)+ROW()-2,1))

Copy C2 down as many rows as you have data in Sheet1

(This col C will be hidden eventually)

Put in B2: =LARGE(C:C,ROW()-1)

Copy B2 down as many rows as you have data in Sheet1

[The above will move all the "valid" work orders
-- for matched rows in col C -- to the top* and
shift all the zeros - for unmatched rows in col C -- to the bottom]
*In descending order

Now to dress up col B ..
---------------------------------
Select col B

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Cell value is | equal to | 0 < type a "zero"

Click Format > Font tab > Color > choose "White"** > OK
**or the color matching the fill / background color

Click OK at the main dialog

(This will mask all cells with "zeros" in col B)

-------------------------

Now hide col C (and also Sheet1), and you're all set to go.

Try a test input in A2, e.g. : 123 (Part no.)

The results [i.e. Work Orders] will appear in B2 downwards

For the test input, based on your sample data,

the results would be: 6010, 6000 (in B2:B3)

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

Is there any way to lookup multiple answers to a lookup formula.

Normally, Excel will stop at the first match it finds and return that as the
answer when, in fact, there may be more than one answer. I want to find the
other matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000 and

6010. I want to do a lookup of Part No. 123 and find ALL the work orders
associated with it (listed separately, of course). Is there any way to do
this in Excel? Thanks

 

[David Byrne]

Bill,

Seems to be the season for data analysis.

Have you explored Pivot Tables??


David

 

[Aladin Akyurek]

Another option...

Let A1:B5 on Sheet1 house the sample you provided, including the labels.

Activate Sheet2.

In B1 enter: 123 (or select this Part No. from a dropdown list you set up on
B1)

In A2 enter: Position [ which is just a label ]

In B2 enter:

=COUNTIF(Sheet1!A:A,B1)

which counts the frequency of occurrence of the target part number.

In A2 enter & copy down:

=IF(COUNTA($A$2:A2)<=$B$2,MATCH($B$1,INDEX(Sheet1!A:A,N(A2)+1):INDEX(Sheet1!
A:A,MATCH(BigNum,Sheet1!A:A)),0)+N(A2),"")

In B2 enter & copy down:

=IF(N($A3),INDEX(Sheet1!B:B,$A3),"")

Is there any way to lookup multiple answers to a lookup formula.

Normally, Excel will stop at the first match it finds and return that as the
answer when, in fact, there may be more than one answer. I want to find the
other matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000 and

6010. I want to do a lookup of Part No. 123 and find ALL the work orders
associated with it (listed separately, of course). Is there any way to do
this in Excel? Thanks

 

[RagDyer]

Seems I did this a few days ago.

Part Num in Column A
Work Order in Column B
Labels in Row 1

Part Number to look up in C1

Data in A2:B100

Enter this formula in C2:

=INDEX(B2:B100,SMALL(IF(A2:A100=C1,ROW(A2:A100)-1,""),ROW(A2:A100)-1))

Now, you have to approximate how many times the part number will appear in
Column A, and then add extra rows of this formula, in order to insure that
all occurrences are returned.

Say that you could have 10 occurrences.
Then drag and copy this formula down for 20 rows, hit F2, and then do
CSE (<Ctrl> <Shift> <Enter>) to make this an array formula.

If it's done correctly, the formulas will automatically be enclosed in curly
brackets.

If there are only 8 part numbers to match C1, the additional rows containing
your formula will return a #NUM! error.

If no errors are returned, you should extend your formula rows in order to
insure that all the part numbers are returned.

This is why you will *always* want to see at least one error !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Is there any way to lookup multiple answers to a lookup formula. Normally,
Excel will stop at the first match it finds and return that as the answer
when, in fact, there may be more than one answer. I want to find the other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000 and
6010. I want to do a lookup of Part No. 123 and find ALL the work orders
associated with it (listed separately, of course). Is there any way to do
this in Excel? Thanks

 

[Aladin Akyurek]

In order to rectify an omission:

BigNum refers to: 9.99999999999999E+307

Another option...

Let A1:B5 on Sheet1 house the sample you provided, including the labels.

Activate Sheet2.

In B1 enter: 123 (or select this Part No. from a dropdown list you set up on
B1)

In A2 enter: Position [ which is just a label ]

In B2 enter:

=COUNTIF(Sheet1!A:A,B1)

which counts the frequency of occurrence of the target part number.

In A2 enter & copy down:

=IF(COUNTA($A$2:A2)<=$B$2,MATCH($B$1,INDEX(Sheet1!A:A,N(A2)+1):INDEX(Sheet1!
A:A,MATCH(BigNum,Sheet1!A:A)),0)+N(A2),"")

In B2 enter & copy down:

=IF(N($A3),INDEX(Sheet1!B:B,$A3),"")

Is there any way to lookup multiple answers to a lookup formula.

Normally, Excel will stop at the first match it finds and return that as the
answer when, in fact, there may be more than one answer. I want to find the
other matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000

and
6010. I want to do a lookup of Part No. 123 and find ALL the work orders
associated with it (listed separately, of course). Is there any way to do
this in Excel? Thanks