Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup needed to search one value and return multipe results
-- KaiChon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup needed to search one value and return multipe results
Refer the below article by Ashish..
http://office.microsoft.com/en-us/ex...260381033.aspx If this post helps click Yes --------------- Jacob Skaria "Seth" wrote: -- KaiChon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup needed to search one value and return multipe results
Another much simpler & faster alternative, imo, is to use just one helper col
to specify the requisite criteria, then you can extract all the multiple line results which will be neatly packed at the top Here's an example using my response to a similar query in the recent past .. I have a worksheet with 5 columns and 600 rows. I need to copy all the rows into a new worksheet that have a specific value in column 3. What formula do I use for this? Here's a formulas set-up to accomplish it .. Assume source data in Sheet1, cols A to E, data from row2 down, key col = col C In another sheet Assume the specific value for col C will be entered in A2 In B2: =IF(A$2="","",IF(Sheet1!C2=A$2,ROW(),"")) Leave B1 empty In C2: =IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SM ALL($B:$B,ROWS($1:1)))) Copy C2 across by 5 cols to G2. Select B2:G2, fill down to cover the max expected extent of source data in Sheet1, say down to G600. Minimize/hide away col B. Cols C to G will return the required results from Sheet1 (dependent on the input in A2), with all lines neatly packed at the top. Success? High five it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup value with multipe Results | Excel Worksheet Functions | |||
VLOOKUP/HLOOKUP to Return Several Results | Excel Worksheet Functions | |||
Return an array of results with a vlookup | Excel Discussion (Misc queries) | |||
Vlookup to return results of If statement | Excel Worksheet Functions | |||
how to return multiple results in vlookup? | Excel Worksheet Functions |