software

I am having trouble in excel returning multiple occurences in vlookup. i
have tried a few of the examples already solved in this community but i keep
getting errors. I have 2 worksheets and want to search the second worksheet
for all occurences of the value entered in the first worksheet.

e.g worksheet 1
A B C D
1 directive 70220 page 12

worksheet 2
A B C D
1 sweden 70220 page 12
2 sweden 70156 page 13
3 sweden 72254 page 14
4 sweden 70220 page 15

(the user enters quot;702202quot;in B1 worksheet1 and i can get the first occurence
of 70220 to return to D1 but not multiple occurences, in this example i would
like to return 12 and 15)

thanks

One play, using non-array formulas ..

Sample construct at:
cjoint.com/?bqmHOezqdh
Extracting Multiple Returns_njclay_wks.xls

Source data is assumed in Sheet2, cols A to D,
from row1 down to row100 say

In Sheet1,

Put in D1:
=IF(ISERROR(SMALL(E:E,ROW(A1))),quot;quot;,
INDEX(Sheet2!D,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

Put in E1:
=IF(Sheet2!B1=quot;quot;,quot;quot;,IF(Sheet2!B1=$B$1,ROW(),quot;quot;))

Select D1:E1, copy down to say, E100,
to cover the max expected extent of data in Sheet2

Col D will return all the page numbers from Sheet2 matching the directive
number input in B1, all neatly bunched at the top
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;njclayquot; gt; wrote in message
...
gt; I am having trouble in excel returning multiple occurences in vlookup. i
gt; have tried a few of the examples already solved in this community but i
keep
gt; getting errors. I have 2 worksheets and want to search the second
worksheet
gt; for all occurences of the value entered in the first worksheet.
gt;
gt; e.g worksheet 1
gt; A B C D
gt; 1 directive 70220 page 12
gt;
gt; worksheet 2
gt; A B C D
gt; 1 sweden 70220 page 12
gt; 2 sweden 70156 page 13
gt; 3 sweden 72254 page 14
gt; 4 sweden 70220 page 15
gt;
gt; (the user enters quot;702202quot;in B1 worksheet1 and i can get the first
occurence
gt; of 70220 to return to D1 but not multiple occurences, in this example i
would
gt; like to return 12 and 15)
gt;
gt; thanks
problem solved, thankyou very much!

quot;Maxquot; wrote:

gt; One play, using non-array formulas ..
gt;
gt; Sample construct at:
gt; cjoint.com/?bqmHOezqdh
gt; Extracting Multiple Returns_njclay_wks.xls
gt;
gt; Source data is assumed in Sheet2, cols A to D,
gt; from row1 down to row100 say
gt;
gt; In Sheet1,
gt;
gt; Put in D1:
gt; =IF(ISERROR(SMALL(E:E,ROW(A1))),quot;quot;,
gt; INDEX(Sheet2!D,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))
gt;
gt; Put in E1:
gt; =IF(Sheet2!B1=quot;quot;,quot;quot;,IF(Sheet2!B1=$B$1,ROW(),quot;quot;))
gt;
gt; Select D1:E1, copy down to say, E100,
gt; to cover the max expected extent of data in Sheet2
gt;
gt; Col D will return all the page numbers from Sheet2 matching the directive
gt; number input in B1, all neatly bunched at the top
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;njclayquot; gt; wrote in message
gt; ...
gt; gt; I am having trouble in excel returning multiple occurences in vlookup. i
gt; gt; have tried a few of the examples already solved in this community but i
gt; keep
gt; gt; getting errors. I have 2 worksheets and want to search the second
gt; worksheet
gt; gt; for all occurences of the value entered in the first worksheet.
gt; gt;
gt; gt; e.g worksheet 1
gt; gt; A B C D
gt; gt; 1 directive 70220 page 12
gt; gt;
gt; gt; worksheet 2
gt; gt; A B C D
gt; gt; 1 sweden 70220 page 12
gt; gt; 2 sweden 70156 page 13
gt; gt; 3 sweden 72254 page 14
gt; gt; 4 sweden 70220 page 15
gt; gt;
gt; gt; (the user enters quot;702202quot;in B1 worksheet1 and i can get the first
gt; occurence
gt; gt; of 70220 to return to D1 but not multiple occurences, in this example i
gt; would
gt; gt; like to return 12 and 15)
gt; gt;
gt; gt; thanks
gt;
gt;
gt;

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;njclayquot; gt; wrote in message
...
gt; problem solved, thankyou very much!