VLookup Question?

[PH NEWS]

Can I get VLookup to ignore certain data.

My situation is this,
Sheet 1 has
ColumnA Column B
Name Job#
Bob 1234
Bob 1235
Bob 1236

On Sheet 2 I have a combo box so I can pick any name from column A on sheet
one, then I want to use Vlookup, or whatever will work, to show me the Job
numbers that a selected person has worked on. So when I pick "bob" from my
combo box in A1, B1:B3 would display 1234, 1235, 1236.
Can anyone help?

 

[Max]

One way ..

In Sheet2, assume cell A1 will contain the selection's result, eg: Bob

Put in A2:
=IF(ISERROR(SMALL(B:B,ROW(A1))),"",
INDEX(Sheet1!B:B,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Put in B2:
=IF(Sheet1!A2="","",IF(Sheet1!A2=$A$1,ROW(),""))
(Leave B1 empty)

Select A2:B2, fill down to say, B10,
to cover the max expected returns for any name in A1

A2:A10 will return the required results for the name in A1,
all neatly bunched at the top

 

[PH NEWS]

Absolutely fantastic Max, thank you very much

SPL

 

[Max]

Delighted !
Thanks for the feedback ..

 

[PH NEWS]

I understand how most of that works, but how does it bunch all the numbers
at the top?

SPL

 

[Max]

I understand how most of that works,
but how does it bunch all the numbers at the top?

Via the part: SMALL(B:B,ROW(A1)) within the MATCH(...)
With the ROW(A1) acting as the incrementer: 1,2,3, ... as we copy down from
the starting cell, the expression SMALL(B:B,ROW(A1)) returns the smallest
number in col B (where we have the criteria to assign arbitrary row numbers
for lines which satisfy), then the 2nd smallest number, the 3rd smallest,
and so on, until all the numbers in col B are exhausted.
(This essentially produces the desired "bunching at the top" effect)

The INDEX(Sheet1!B:B,MATCH(...)) then translates the expressions' returns
accordingly to the final outputs which are the job#s in Sheet1's col B.

 

[Max]

You're welcome !