Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple matches using LOOKUP
Hello everyone, Here is what I am trying to accomplish. Based on Column A or ORDER_NOTE I need to find the respective match in column C. It perfectly works for 1 match. What if I have three or more matches how to force excel to look for others? This formula is inside of macro. I can use count function to find out how many times 'ORDER_NOTE' appeared. ActiveCell.FormulaR1C1 = "=LOOKUP(""ORDER_NOTE"",Range(""A:A""),Range(""C:C ""))" Also, is it possible to concatenate all the finding of column C that match 'ORDER_NOTE' in column A? Also, is there a way to assign an address a cell? Thank you, Sonya -- Sonya795 ------------------------------------------------------------------------ Sonya795's Profile: http://www.excelforum.com/member.php...o&userid=26110 View this thread: http://www.excelforum.com/showthread...hreadid=394561 |
#2
|
|||
|
|||
If the cell containing the lookup value (ORDER_Note in your case) is
named "luVal", the range containing the data (A:C in your case) is named "Tbl", and the cell containing the number of the lookup column within Tbl (3 in your case) is named "luCol", then using built-in functions, array enter and fill down: =IF(ROWS(A$1:A1)<=COUNTIF(INDEX(Tbl,0,1),luVal),IN DEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)=luV al,ROW(INDIRECT("A1:A"&ROWS(Tbl)))),ROWS(A$1:A1))) ,"") or, if the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, enter and fill down: =IF(ISERROR(INDEX(vlookups(luVal,Tbl,luCol),ROW(A1 ))),"",INDEX(vlookups(luVal,Tbl,luCol),ROW(A1))) Alan Beban Sonya795 wrote: Hello everyone, Here is what I am trying to accomplish. Based on Column A or ORDER_NOTE I need to find the respective match in column C. It perfectly works for 1 match. What if I have three or more matches how to force excel to look for others? . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple matches using LOOKUP | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Multiple lookup value's | Excel Worksheet Functions | |||
lookup multiple occurrences of a value excel | Excel Worksheet Functions | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions |