Lookup criteria from dropdown list and return row

[shreeja178]

Hi,

pls help me with a excel formula to get result as in the attached file.I tried using autofilter but not able to acheive.

Thanks
Shreeja

[Naveed Raza]

do you want MACRO or Function ?

[shreeja178]

excel formulas will be good.

[:) Sixthsense :)]

In C17 Cell - Array Formula - Requires CTRL+SHIFT+ENTER


=IFERROR(INDEX($C$2:$E$11,SMALL(IF($C$2:$C$11=$C$14,ROW($C$2:$C$11)-MIN(ROW($C$2:$C$11))+1),ROWS($1:1)),COLUMNS($A:A)),"")


Drag it down and right...

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

[Naveed Raza]

Try this Attached macro file

is it what u looking for

[shreeja178]

Thanks sixth sense, it works well..
But I need to pull only "PENDING" from HEADER 2. this formula pulls all rows based on drop down criteria from HEADER 1.
I need to filter drop down criteria from HEADER 1 and criteria "PENDING" (fixed) from HEADER 2 and return entire available rows.

[Naveed Raza]

I Just add that criteria to Sixthsense Function

=IFERROR(INDEX($C$2:$E$11,SMALL(IF(($C$2:$C$11=$C$14)*($D$2:$D$11="PENDING"),ROW($C$2:$C$11)-MIN(ROW($C$2:$C$11))+1),ROWS($1:1)),COLUMNS($A:A)),"")

replace that with above

[:) Sixthsense :)]

But I need to pull only "PENDING" from HEADER 2. this formula pulls all rows based on drop down criteria from HEADER 1.

Try this revised formula

=IFERROR(INDEX($C$2:$E$11,SMALL(IF(($C$2:$C$11=$C$14)*($D$2:$D$11="Pending"),ROW($C$2:$C$11)-MIN(ROW($C$2:$C$11))+1),ROWS($1:1)),COLUMNS($A:A)),"")


Edit: Hm... Naveed Raza has already given the addition of condition

[shreeja178]

Thanks Naveed & thanks Sixth sense.. both works perfectly.

[Naveed Raza]

Glad to Help & thanks for Rep

[boopathiraja]

hI, Try this in C17 and drag it across and down

Please Login or Register  to view this content.

[:) Sixthsense :)]

Glad to Help & thanks for Rep

Ha ha No rep for me (from the OP) lol