lookup multiple values

N

njclay

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 "702202"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
 
M

Max

One play, using non-array formulas ..

Sample construct at:
http://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))),"",
INDEX(Sheet2!D:D,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

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

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
 
N

njclay

problem solved, thankyou very much!

Max said:
One play, using non-array formulas ..

Sample construct at:
http://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))),"",
INDEX(Sheet2!D:D,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

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

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
Click to expand...
 
Top