Home |
Search |
Today's Posts |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup when multiple values can be returned
Try this (still an array formula) but with 1000+ rows *expect* it to be
slow: =IF(COLUMNS($B2:B2)<=COUNTIF(ToolNum,$A2),INDEX(To olOrd,SMALL(IF(ToolNum=$A2,ROW(ToolOrd)-MIN(ROW(ToolOrd))+1),COLUMNS($B2:B2))),"") Copy across -- Biff Microsoft Excel MVP wrote in message ... On Feb 18, 3:55 pm, "T. Valko" wrote: Try this array formula** : Whe ToolNum = Sheet1!A2:An ToolOrd = Sheet1!B2:Bn Enter this array formula** on Sheet2 B2: =IF(ROWS(B$2:B2)<=COUNTIF(ToolNum,A$2),INDEX(ToolO rd,SMALL(IF(ToolNum=A$2,R*OW(ToolOrd)-MIN(ROW(ToolOrd))+1),ROWS(B$2:B2))),"") Copy down until you get blanks. You'll have to copy to a number of cells that is equal to the maximum number of times any lookup_value appears in the lookup_table. For example, in your sample data the lookup_value J123 appears the max number of times, 2. So you need to copy the formula to at least 2 cells. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... I have a spreadsheet with data in the following manner Sheet 1 Col A. Col. B Tool Number Tool Order # J123 T008 J598 T258 J123 T568 On Sheet 2 in Column A, cell A2 I have the values I would like to lookup in Sheet 1 Col A. I need to return all values in Column B (Tool Order) on Sheet 2. Anyone have any insight? Thanks, Dennis- Hide quoted text - - Show quoted text - Thanks for the reply. What would I need to do to get this where the different Tool Orders could be displayed in different columns on the same row? To clarify, my Sheet 2 has 1000+ rows of Tool Numbers to match to sheet 1. Thanks. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple returned values into a single cell | Excel Discussion (Misc queries) | |||
N/A returned on VLOOKUP formula | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Can I get multiple values returned for an IF formula? | New Users to Excel | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions |