How to use a VLOOKUP across multiple columns?

[HelpMeWithExcelPlease]

Hi all,

I'm hoping someone has an answer to this issue that I am am running in to. I am working with a not so eloquently designed spreadsheet to begin with, but hopefully that won't bog down what I am trying to do. Here is a simplified version:
Table 1

Defect ID (Column A)	State 1 (Column B)	State 2 (Column C)	State 3 (Column D)
1	Texas
2	California	Texas
3	Colorado	Maine	Texas
4	New York	Colorado

<tbody>
</tbody>

Table 2

​

State Name (Column A)	# of Defects (Column B)
Texas	3
California	1
Colorado	2
Maine	1
New York	1

<tbody>
</tbody>

What formula do I need to put in Table 2, Column B to be able to double-click on the cell and open up Table 1 to be filtered to rows that only show the Defects associated with the corresponding state? Is it a version of a VLOOKUP?

Any insight into this issue would be greatly appreciate, thank you in advance!

 

[Jonmo1]

You could use countif...​

=COUNTIF(B2:D5,A8)

A8 is a cell holding the state to count, say Texas

 

[HelpMeWithExcelPlease]

Hey Jonmo1,

Thanks for the response! That'll work to do a count of how many defects are associated with the state, but I am looking for a way to not only see the count of the number of defects but to be able to double-click on the count and pull up the rows that make up that count - just as you can do in a pivot table. Any ideas on how to make that happen?