Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup question | Excel Worksheet Functions | |||
VLOOKUP() Question... i hope | Excel Discussion (Misc queries) | |||
Vlookup question please | Excel Worksheet Functions | |||
vlookup question please | Excel Worksheet Functions | |||
Vlookup against multiple columns/worksheets question | Excel Discussion (Misc queries) |