vlookup with match
 

Here is the code:
=VLOOKUP(S7,A4:A1000,MATCH(T3,B4:B1000,0)+1,FALSE)
I am getting a #ref error.

s7 is the date I am looing for in a column and t3 is text that I am looking
for. What I want is to first look by the date and if the date matches it
looks for the text and if both match it will add the number of times that it
sees those 2 together and return that value. Any ideas on how to get this to
work?

vlookup with match
 

Maybe

=SUMPRODUCT((A4:A1000=S7)*(B4:B1000=T3))

Mike

"tim2216" wrote:

Here is the code:
=VLOOKUP(S7,A4:A1000,MATCH(T3,B4:B1000,0)+1,FALSE)
I am getting a #ref error.

s7 is the date I am looing for in a column and t3 is text that I am looking
for. What I want is to first look by the date and if the date matches it
looks for the text and if both match it will add the number of times that it
sees those 2 together and return that value. Any ideas on how to get this to
work?

vlookup with match
 

So far so good is there an easy way to copy this or is do I need to enter
this into every cell? I have close to 500 cells.

"Mike H" wrote:

Maybe

=SUMPRODUCT((A4:A1000=S7)*(B4:B1000=T3))

Mike

"tim2216" wrote:

Here is the code:
=VLOOKUP(S7,A4:A1000,MATCH(T3,B4:B1000,0)+1,FALSE)
I am getting a #ref error.

s7 is the date I am looing for in a column and t3 is text that I am looking
for. What I want is to first look by the date and if the date matches it
looks for the text and if both match it will add the number of times that it
sees those 2 together and return that value. Any ideas on how to get this to
work?

vlookup with match
 

If I understand correctly, you want to copy this logic to other cells
programmatically:

activecell.copy
range(activecell,activecell.offset(500,0)).paste
application.cutcopymode = false

Remarks:

1. The offset property uses the r,c convention - therefore, the code above
tells Excel to select a range that is 501 rows by 1 column, relative to the
activecell's address

1. You may want to freeze relevant cells in your SUMPRODUCT formula:
=SUMPRODUCT(($A$4:$A$1000=S7)*($B$4:$B$1000=T3))

HTH



"tim2216" wrote:

So far so good is there an easy way to copy this or is do I need to enter
this into every cell? I have close to 500 cells.

"Mike H" wrote:

Maybe

=SUMPRODUCT((A4:A1000=S7)*(B4:B1000=T3))

Mike

"tim2216" wrote:

Here is the code:
=VLOOKUP(S7,A4:A1000,MATCH(T3,B4:B1000,0)+1,FALSE)
I am getting a #ref error.

s7 is the date I am looing for in a column and t3 is text that I am looking
for. What I want is to first look by the date and if the date matches it
looks for the text and if both match it will add the number of times that it
sees those 2 together and return that value. Any ideas on how to get this to
work?