Multiple matches using LOOKUP

[Sonya795]

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

[Alan Beban]

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),INDEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)=luVal,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? . . .