how to lookup a value in a list and return multiple corresponding
 

I have tried over and over to put in the formula to return multiple values.
I have spent the last several hours trying to figure this one out, and I am
at wits end. I got it to return a value but it is not correct. I am not
sure why it is even returning that one vallue.

I have two sheets in one workbook. One sheet has my data, the second sheet
has the lookup value to look up on the data table. Where could I get more
help. It seems I am getting the wrong value and also only one value per
cell.

This is the formula I have entered: "
=INDEX('Sales TRX smartlist'!$B$1:$B$2482,SMALL(IF('Sales TRX
smartlist'!$A$1:$A$2482='Aug All'!C4,ROW('Sales TRX
smartlist'!$A$2:$A$2482)),ROW('Sales TRX smartlist'!1:2482)))

how to lookup a value in a list and return multiple corresponding
 

Try it like this (I'm leaving out the sheet names to make it easier to
read):


=INDEX($B$1:$B$2482,SMALL(IF($A$1:$A$2482=C$4,ROW( $A$1:$A$2482)),ROWS(A$1:A1))-MIN(ROW(A$1:A$2482))+1)

Don't forget to array enter!

Copy down as needed


--
Biff
Microsoft Excel MVP


"Catalina" wrote in message
...
I have tried over and over to put in the formula to return multiple values.
I have spent the last several hours trying to figure this one out, and I
am
at wits end. I got it to return a value but it is not correct. I am not
sure why it is even returning that one vallue.

I have two sheets in one workbook. One sheet has my data, the second
sheet
has the lookup value to look up on the data table. Where could I get more
help. It seems I am getting the wrong value and also only one value per
cell.

This is the formula I have entered: "
=INDEX('Sales TRX smartlist'!$B$1:$B$2482,SMALL(IF('Sales TRX
smartlist'!$A$1:$A$2482='Aug All'!C4,ROW('Sales TRX
smartlist'!$A$2:$A$2482)),ROW('Sales TRX smartlist'!1:2482)))

how to lookup a value in a list and return multiple corresponding
 

Hi,

Please try my solution at the following link

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Catalina" wrote in message
...
I have tried over and over to put in the formula to return multiple
values.
I have spent the last several hours trying to figure this one out, and I
am
at wits end. I got it to return a value but it is not correct. I am not
sure why it is even returning that one vallue.

I have two sheets in one workbook. One sheet has my data, the second
sheet
has the lookup value to look up on the data table. Where could I get more
help. It seems I am getting the wrong value and also only one value per
cell.

This is the formula I have entered: "
=INDEX('Sales TRX smartlist'!$B$1:$B$2482,SMALL(IF('Sales TRX
smartlist'!$A$1:$A$2482='Aug All'!C4,ROW('Sales TRX
smartlist'!$A$2:$A$2482)),ROW('Sales TRX smartlist'!1:2482)))