Vlookup, Match, Count

[camle]

I am looking for a way to look in a table or list and count if they match.
Ex.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]American Pickers
[/TD]
[TD]2011x01
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2011x02
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2012x01
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2012x02
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2012x03
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x01
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x02
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x02
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x02
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]10x01
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

Output should be:
American Pickers
2011=2
2012=3

Bones:
9=4
10=1

Hope I explain well enough.
Thanks

 

[Gerald Higgins]

A Pivot Table could do this for you very easily.

 

[shahfahad]

=countifs($a$1:$a$10,a9,$b$1:$b$10,b9,$c$1:$c$10,c9)

 

[camle]

=countifs($a$1:$a$10,a9,$b$1:$b$10,b9,$c$1:$c$10,c9)

I will look into these options.

Thanks Guys

 

[camle]

I guess I need to give more info.



[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]American Pickers
[/TD]
[TD]2011x01[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]American Pickers[/TD]
[TD]2011x02[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]American Pickers[/TD]
[TD]2012x01[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]American Pickers[/TD]
[TD]2012x02[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]American Pickers[/TD]
[TD]2012x03[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]Bones[/TD]
[TD]9x01[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Bones[/TD]
[TD]9x02[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Bones[/TD]
[TD]9x02[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Bones[/TD]
[TD]9x02[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Bones[/TD]
[TD]10x01[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

The List above is a workbook (Tv DataBase) that gets a list of files from folders on the hard drive. Works great!

I have another workbook (TVDB) that pull tv show info from TVDB.com, it gets: Show names, season#, episode#, date aired

I want to compare (TVBD) workbook to the (Tv DataBase) Workbook.

in the (Tv DataBase) Workbook there are Tabs with each Tv Show Ex. Below

Tab (American Pickers)

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Season
[/TD]
[TD]Episodes
[/TD]
[TD]On the Hard drive
[/TD]
[TD]Need to get
[/TD]
[/TR]
[TR]
[TD]2010
[/TD]
[TD]29
[/TD]
[TD]20
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]2011
[/TD]
[TD]26
[/TD]
[TD]25
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2012
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]2013
[/TD]
[TD]33
[/TD]
[TD]33
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Under the (On the hard drive) column:
How can I lookup American Pickers and count how many season 2010's there are?

A lot of info sorry

Thanks

 

[shahfahad]

Use VLOOKUP.

=VLOOKUP(Workbook1,$A$1:$C$10!Worbook2,3,0)

 

[camle]

Is this formula complete. how does it work

<colgroup><col width="116"></colgroup><tbody>
[TD="width: 116, align: center"][/TD]

</tbody>

 

[shahfahad]

It looks up the value of Workbook 1 in Workbook 2 and returns the value in 3rd column

 

[camle]

I get #Value!

 

[camle]

It looks up the value of Workbook 1 in Workbook 2 and returns the value in 3rd column

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]American Pickers
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Season
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Episodes
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Have[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Need[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Complete
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2010
[/TD]
[TD]29
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]2011
[/TD]
[TD]26
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2012
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2013
[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Specials
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2014
[/TD]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Cells above are on Tab (American Picker)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]American Pickers
[/TD]
[TD]2010x01
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]American Pickers
[/TD]
[TD]2010x02
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]American Pickers
[/TD]
[TD]2010x03
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]American Pickers
[/TD]
[TD]2010x04
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]American Pickers
[/TD]
[TD]2010x05
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]American Pickers
[/TD]
[TD]2011x01
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]American Pickers
[/TD]
[TD]2011x02
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]American Pickers
[/TD]
[TD]2011x03
[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]American Pickers
[/TD]
[TD]2011x04
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]American Pickers
[/TD]
[TD]2012x01
[/TD]
[TD]2012
[/TD]
[/TR]
</tbody>[/TABLE]

Cells above are from Tab(Getfiles) this list comes from reading files from the hard drive.

I need a Formula in C4 to lookup A1(American Pickers) and A4(2010) in Tab (Getfiles) list of data and count how A4(2010)'s there are.

Cell (C4) should = 5
Cell (C5) Should = 4
Cell (C6) Should = 1
and so on down the list

It's kicking my butt....

Thanks