Formula? Index and Match?

[trumpetman]

I have attached a excel sheet with some data.

I am wondering what formula would be if I want to look at

a) all the accounts that are opened with all the different products. Looking to exclude all the closed

b) all the gold accounts that are opened.

Is it an index and match that would do this? If so what would that look like?

Thanks

[Pete_UK]

It is probably easier to apply autofilter to the columns.

For your first query unselect all but Blanks in the filter drop-down on Column D.

For the second query, unselect all but Gold on column B.

If you wish, you can select all the visible data, click <copy> then paste to a new sheet.

Hope this helps.

Pete

[Jul Stev]

Hi trumpetman,

Try see attached file

Data Exerciseupdate.xlsx

[ramananhrm]

If you require the values of the specified condition, then you can try this file.

[jayatu chatterjee]

=IFERROR(INDEX($A$2:$A$70,(SMALL(IF($B$2:$B$70="Gold",ROW($B$2:$B$70),""),(COUNTIF($B$2:$B$70,"Gold")-(ROW()-2)))-1)),"")

Type this formula on column G. This formula will give all the account number where type of account is gold. as it is a array formula you have to press ctrl+shft+enter and copy it to doen the column