Lookup one value and return multiple corresponding values
 

I want a formula to look for the value "Place an Order" in column E and
return the value from the same in row in column B. I've found a formula from
the power user corner on the office website which I think applies but I don't
fully understand it and I am having trouble getting it work correctly. The
formula I'm trying to make work is as follows:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

My worksheet data runs from B4:E34 so i've tried changing the formula to
look like below but it's only returning some of the correct values and 0 for
anything else.
=INDEX($B$4:$E$34,SMALL(IF($E$4:$E$34="Place an
Order",ROW($E$4:$E$34)),ROW(1:1)),1)

Anything I'm doing wrong or another way of doing it would be much appreciated

Lookup one value and return multiple corresponding values
 

Try this and enter with Ctrl+Shift+Enter ...{} will appear round the formula:

=INDEX($B$1:$E$34,SMALL(IF($E$1:$E$34="Place an
Order",ROW($E$1:$E$34)),ROW(1:1)),1)

HTH

"StephenB" wrote:

I want a formula to look for the value "Place an Order" in column E and
return the value from the same in row in column B. I've found a formula from
the power user corner on the office website which I think applies but I don't
fully understand it and I am having trouble getting it work correctly. The
formula I'm trying to make work is as follows:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

My worksheet data runs from B4:E34 so i've tried changing the formula to
look like below but it's only returning some of the correct values and 0 for
anything else.
=INDEX($B$4:$E$34,SMALL(IF($E$4:$E$34="Place an
Order",ROW($E$4:$E$34)),ROW(1:1)),1)

Anything I'm doing wrong or another way of doing it would be much appreciated