Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup multiple values
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup multiple values
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "njclay" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup multiple values
problem solved, thankyou very much!
"Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "njclay" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup multiple values
You're welcome !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "njclay" wrote in message ... problem solved, thankyou very much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to lookup a value and return multiple corresponding values | Excel Worksheet Functions | |||
Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How to sum values in multiple worksheets | Excel Worksheet Functions | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions |