Lookup with multiple results

[wmjenner]

Is there any way that Excel can do a lookup where there are multiple
values associated with the lookup parameter? Here's an example:

Part No. Work Order
123 8000
123 8001
234 8002
567 8003

I want to know what (production) work orders are open for a given
finished product (part no.). Part No. 123 has two work orders
associated with it - 8000 and 8001. Normally, Excel finds the first
match and that's the end of it. Is there a way to look up ALL of the
values meeting the criteria?

Thanks

 

[Max]

If Autofilter is not an option, try this:

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)

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

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 of "123", based on your sample data,
the results would be: 8001, 8000 (in B2:B3)

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLookups(123,A2:C5,2) array entered into a column of at least enough
cells to accommodate the output.

Alan Beban

 

[wmjenner]

This is exactly what I'm looking for. Unfortunately, I can't get it t
work. I found the procedure in the macro modules, but I am getting th
error message #NAME?.

Just for clarification, could you confirm that I am setting it u
right?

=vlookups(A$1,Sheet1!$b2:$b300,3)

a$1 is the lookup value (as in the vlookup formula)
Sheet1!$b2:$b300 is the range, where the first column contains th
lookup values.
"3" is the column in the above range from which the values are to b
returned.

Is that right?

Many thanks.

Bil

 

[Max]

Think firstly, you need to copy the entire module 1 in Alan's
"ArrayFunctions.xls"
over to your workbook first (this should take care of the #NAME errors).

Your syntax looks fine but it might be the way you set-up / enter the
formula.

In another sheet say, Sheet2,
you have to select a column* range of cells first,
let's say A2:A21 (assuming A1 will contain the lookup value)
[*with as Alan says: "of at least enough cells to accommodate the output."]

Then put in the formula bar: =Vlookups(A$1,Sheet1!$B2:$B300,3)
and press Ctrl + Shift + Enter (instead of just Enter key alone)
to array-enter it.

You can then fill / copy "A2:A21" across the columns,
to read other lookup values you may have in B1, C1, etc.
(assuming the column range of 20 cells is enough for all lookup values)

 

[wmjenner]

Thanks for your efforts, Max (and also for your previous reply which,
unfortunately, is a little over my head.

As for Alan's formula, I think I've tried it all. The #NAME? error
won't go away (it does when I replace vlookups with vlookup (just as a
test), so that tells me my workbook still isn't finding his formula. I
copied the entire ArrayFunctions into my workbook as a separate sheet
(although it is already present as a macro module in VB from before).
So I don't get it - it seems to not find it even though I can see it.


Any other ideas? I could send you the file since you obviously are
light years ahead of me in Excel. E-mail me if you like
([email protected]) and I can e-mail it back (I promise I'm legit!).

Many thanks in advance!

 

[Max]

Apologies, I missed a correction in your formula syntax earlier

The line:

Then put in the formula bar: =Vlookups(A$1,Sheet1!$B2:$B300,3)

should read

Then put in the formula bar: =Vlookups(A$1,Sheet1!$A2:$C300,3)

The above assumes that the "Vlookups" table_array in Sheet1 is in A2:C300,
and you want to retrieve the values in the 3rd col, ie col C,
since you indicated a "3" in your sample formula.

Your original "Sheet1!$B2:$B300" as the table_array reference was incorrect

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

Okay, now let's try tackling the #NAME error ...

All module / code copying is done in VBE, not in the worksheets !

This is essentially what I did:

Open Alan's "ArrayFunctions.xls"
Press Alt + F11 to go to VBE

In the Project Explorer window (on the left bar)
Look for "MyPers(ArrayFunctions.xls)"
then look for Modules > Module1 just below
Double-click to open Module1
(what's opened will appear on the right of the screen, viz. the code)
Do a Ctrl + A to select all of Alan's code > Copy

Now go to your own workbook in Project Explorer
Look for "VBAProject (<Yourfilename>)
Click on it then click Insert > Module
Right-click > Paste
(this will paste Alan's module1's contents into a module in your workbook)
Press Alt + Q to exit and return to Excel

Give the above a bash !

You should be able get it this time <g>

 

[Peo Sjoblom]

In the Project Explorer window (on the left bar)

Look for "MyPers(ArrayFunctions.xls)"
then look for Modules > Module1 just below
Double-click to open Module1
(what's opened will appear on the right of the screen, viz. the code)
Do a Ctrl + A to select all of Alan's code > Copy

Now go to your own workbook in Project Explorer
Look for "VBAProject (<Yourfilename>)
Click on it then click Insert > Module
Right-click > Paste
(this will paste Alan's module1's contents into a module in your workbook)
Press Alt + Q to exit and return to Excel

Or you could just grab the module with the mouse and drag it to the other
workbook

 

[Max]

Apologies, I missed a correction in your formula syntax earlier

The line:

Then put in the formula bar: =Vlookups(A$1,Sheet1!$B2:$B300,3)

should read

Then put in the formula bar: =Vlookups(A$1,Sheet1!$A2:$C300,3)

The above assumes that the "Vlookups" table_array in Sheet1 is in A2:C300,
and you want to retrieve the values in the 3rd col, ie col C,
since you indicated a "3" in your sample formula.

Your original "Sheet1!$B2:$B300" as the table_array reference was incorrect

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

Okay, now let's try tackling the #NAME error ...

All module / code copying is done in VBE, not in the worksheets !

This is essentially what I did:

Open Alan's "ArrayFunctions.xls"
Press Alt + F11 to go to VBE

In the Project Explorer window (on the left bar)
Look for "MyPers(ArrayFunctions.xls)"
then look for Modules > Module1 just below
Double-click to open Module1
(what's opened will appear on the right of the screen, viz. the code)
Do a Ctrl + A to select all of Alan's code > Copy

Now go to your own workbook in Project Explorer
Look for "VBAProject (<Yourfilename>)
Click on it then click Insert > Module
Right-click > Paste
(this will paste Alan's module1's contents into a module in your workbook)
Press Alt + Q to exit and return to Excel

Give the above a bash !

You should be able get it this time <g>

 

[Max]

Or you could just grab the module with the mouse
and drag it to the other workbook

But of course <g>, thanks, Peo !

 

[Alan Beban]

Another possibility one might want to consider is to download the
ArrayFunctions file and save it as an Add-in. Then, in any workbook in
which one wants to use any of the functions, go to the VBE Editor, click
on Tools|References, and check the add-in. All the functions should
then be available to that workbook. This avoids having to drag the
entire Module into every workbook in which one wants to use one or more
of the functions.

Alan Beban

 

[Alan Beban]

Max, See my post in response to Peo Sjoblom's post in this thread.

Alan Beban

 

[Max]

Thanks, Alan ! Good idea.

Did what you suggested
and it works great

But I checked the add-in
in Excel itself, via Tools > Add-ins
rather than via Tools > References in VBE
(I couldn't find it in VBE??)

 

[Max]

Hi, I've just sent you 2 example files via private email to illustrate
Alan's and my suggested solutions as applied to your sample situation
(i.e. Work Order values are assumed to be in col C, Part No. in col A)

I've put some helpful notes inside each file.

I've also placed Alan's code inside the file (for his solution)
so it should definitely work now, no more #NAME? errors!
[but do check out Alan's reply on how to make it into an add-in]

Hope you find the files useful.

Drop us a line or 2 here on how it went for you, ok ?

 

[Max]

Seen, and replied therein, thanks.

 

[wmjenner]

The files are extremely useful and are going right into my Excel help
file. Many thanks for sending them. Your solution is the only one
that I can get to work so far. It works great. The only shortcoming
is that I would like to have ALL of the part numbers listed with ALL of
the work orders. Alan's does that (in your sample file). But Alan's
doesn't work in the "real" file. I compared your vlookups formula to
mine and it appears to be identical in structure, yet mine doesn't work
(the column is filled with the same repeating work order number). And
in one column, a message line ("Select at least 2 row(s)") from a
completely unrelated procedure in Alan's file is pulled in and
populates the entire column. That tells me there's a glitch
somewhere.

I've sent you my file - perhaps you can take a look and see if there's
an obvious problem.

Thanks.

Bill

 

[Alan Beban]

If you're not coming to conclusion on this and would like to email me
your file, post with an indication of your email address and I will
email you my email address.

Alan Beban

 

[Max]

You're welcome !
Glad you found the files useful.

Yes, Alan's solution is most appropriate
for your particular situation

ok, the "glitch" is that you probably
did not set-up or enter Alan's *array*
formulae properly in your Sheet2.

Recollect the lines in my earlier post:

...press Ctrl + Shift + Enter
(instead of just Enter key alone)
to array-enter it.

This is the crux of the remaining problems you faced.

[I've since done the corrections and emailed the file back
to you (to the 2nd email account) a couple of hours ago.]

The steps to do it correctly in Sheet2 are:

You have your part nos in row1 across
(in A1, B1, C1, etc) in Sheet2

Select say, A2:A66
(assuming a 60+ cell range is sufficient for ALL cases)

Put in the *formula bar*:
=vlookups(A$1,Master!$A$15:$C$300,3)

Now *array-enter* the formula, which means:
---------------------------------------------------
While holding down Ctrl + Shift keys, press Enter
(instead of just pressing Enter key alone)

Done correctly, Excel will wrap curly braces around
the formula viz:
{=vlookups(A$1,Master!$A$15:$C$300,3)}

Note: Do not type-in the curly braces yourself.

-------------------------------------------
Copying A2:A66 across to say W2:W66
(assuming W1 is the last part no. in row1)

With A2:A66 still selected (i.e. highlighted)

Scroll to cell A66

Point your cursor to the bottom right corner of A66
(cursor will turn into a black cross "+")

Drag this cross across to cell W66

The above will copy the array formula in A2:A66 across
to W2:W66

 

[wmjenner]

Many thanks to both Alan and Max on this. Alan's vlookups is exactly
what I've been looking for - for years actually! I can't imagine why
this isn't a standard Excel formula because it's extremely useful in so
many applications. And with Max's help I finally got it configured
properly so that it now works flawlessly.

Thanks to you both!

Bill

 

[Alan Beban]

De nada. Thanks for the kind words.

Alan Beban