Extract Data using Index Match Small If

[Jerseyspud]

Hi
I would be grateful for your help. I have a spreadsheet (attached) with a list of account names, values and account status (open or closed).

I need to extract, in descending order, the account names and values that have an "open" status. I can extract the data in descending order using index Index, Match, Small and Rows, but dont know how to add a criteria that returns accounts that have an open status.

Your help is greatly appreciated.

Thanks
Nick

 

[Khalid NGO]

Hi Nick,
Firstly, welcome to the forum Chandoo.org...

I believe your current formulas in column G and H does not requires special key combinations.

To answer your question...
i have slightly amend your formula as

in H2:
=IFERROR(INDEX(Value,MATCH(SMALL(IF(Status="Open",Value,""),ROWS($H$2:H2)),Value,0)),"")
You now have to enter with Ctrl+Shift+Enter

in G2:
=IFERROR(INDEX(Name,MATCH($H2,Value,0)),"")
with just enter

copy down both...

Let me know if it meets your requirement.

Regards,

 

[Simayan]

Hi,

An alternate solution using Advanced Filter & a small Vb coding for reuse of same file.

Hope this suits your query