Index Match with an or function

[BWellman]

Hey all,

I have a table with two columns. One column is names, the second column is their class (1, 2, 3, or 4). I am looking to create a formula that shows all of the names from class 1, 2, or 3, but eliminates all of the blanks for the rest of the cells.

I have attached a sample file as an example.

Regards,
B

[sandy666]

you can try Pivot Table

[BWellman]

I'm going to have a bunch of tabs that will update pretty regularly. Do you know if it's possible to do with a formula?

Thanks,
B

[Pete_UK]

You could use column A as a helper column, with this formula in A3:

=IF(C3<=3,MAX(A$2:A2)+1,"-")

Copy this down to the bottom of your list. Then you can use this formula in E3:

=IFERROR(INDEX($B$3:$B$18,MATCH(ROWS($1:1),$A$3:$A$18,0)),"")

and copy this down.

Hope this helps.

Pete

[sandy666]

I'm going to have a bunch of tabs that will update pretty regularly

You can use PivotTable for many sheets and update all of them

[BWellman]

Pete - In the file I'm actually using, the column C values are all words, but your way would still work with an added column. I can start with that because I'm assuming it's not possible to combine a match with either:
Or (one of three values)
<> does not equl

Sandy - Can't use pivot tables, sorry Thanks for the help though!

[sandy666]

I wonder why, but I think there is a serious reason. No problem, try formulas. good luck

thanks for the feedback