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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Defect ID (Column A)[/TD]
[TD="align: center"]State 1 (Column B)[/TD]
[TD="align: center"]State 2 (Column C)[/TD]
[TD="align: center"]State 3 (Column D)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Texas[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]California[/TD]
[TD]Texas[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Colorado[/TD]
[TD]Maine[/TD]
[TD]Texas[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]New York[/TD]
[TD]Colorado[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2

​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]State Name (Column A)[/TD]
[TD="align: center"]# of Defects (Column B)[/TD]
[/TR]
[TR]
[TD]Texas
[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Colorado[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Maine[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

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?