Lookup based on Cell Criteria

[nicci113]

Hi There,

I'm half way into the supposedly easy calculation, but need some help. I've entered the formula which when equal to "approve" should give me only the details where it matches from another sheet. So if its "approve" then it should give me the name and other details.. I've attached the spreadhseet to make some sense, I need some help here please.

Thanks,

[Richard Buttrey]

Personally I'd use a Pivot Table and avoid the need for any formulae.

See attached. Since you've only apparently got Excel 2003 I've put the Request Status in the Report Filter area of the PT Field List. If you have Excel 2010 or later it would be more elegant to use the Slicer functionality.

[nicci113]

Richard, Thanks for the help.. I'm not sure why it came up as 2003, im on office 365 so the latest, I know how to use the slicer, but the requestor insisted on some format, which made me use formula only..

[Richard Buttrey]

Richard, Thanks for the help.. I'm not sure why it came up as 2003, im on office 365 so the latest, I know how to use the slicer, but the requestor insisted on some format, which made me use formula only..

Hi,

Your profile is showing Excel 2003. To avoid any confusion in the future it would be a good idea to change it.

[avk]

Array formula ensure [shift+ctrl+enter]
In "A3"

=IFERROR(INDEX(owssvr!F:F,SMALL(IF($F$1=owssvr!$E$2:$E$8,ROW(owssvr!$E$2:$E$8)),ROWS(A$2:A2))),"")

In "B3"

=IFERROR(INDEX(owssvr!B:B,SMALL(IF($F$1=owssvr!$E$2:$E$8,ROW(owssvr!$E$2:$E$8)),ROWS(B$2:B2))),"")

In "C3"

=IFERROR(INDEX(owssvr!C:C,SMALL(IF($F$1=owssvr!$E$2:$E$8,ROW(owssvr!$E$2:$E$8)),ROWS(C$2:C2))),"")

In "D3"

=IFERROR(INDEX(owssvr!D:D,SMALL(IF($F$1=owssvr!$E$2:$E$8,ROW(owssvr!$E$2:$E$8)),ROWS(D$2:D2))),"")

copy down till blank.

[nicci113]

Smashing brilliant. I know I'm asking more, but can you break down the formula post small so I understand a bit better. Please