VLookup Question?
 

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?

VLookup Question?
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PH NEWS" wrote in message
...
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?

VLookup Question?
 

Absolutely fantastic Max, thank you very much

SPL

"Max" wrote in message
...
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PH NEWS" wrote in message
...
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?

VLookup Question?
 

Delighted !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PH NEWS" wrote in message
...
Absolutely fantastic Max, thank you very much

SPL

VLookup Question?
 

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

SPL


"Max" wrote in message
...
Delighted !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PH NEWS" wrote in message
...
Absolutely fantastic Max, thank you very much

SPL

VLookup Question?
 

"PH NEWS" wrote
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

VLookup Question?
 

Once again, thank you Max, much obliged, this has really helped me out and I
always feel better if I know how something is working.

Cheers,

SPL

"Max" wrote in message
...
"PH NEWS" wrote
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

VLookup Question?
 

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PH NEWS" wrote in message
...
Once again, thank you Max, much obliged, this has really helped me out and
I
always feel better if I know how something is working.

Cheers,

SPL