VLOOKUP used only when IF condition is met

[Bradley]

I am trying to write and IF statement with a VLOOKUP. I only want the value
to be returned when the IF column is equal to a set value.

For example, I have a data worksheet which has months and document #'s and
total hits. I am creating another worksheet that wil search the data page
and return the number of hits for the document, but I want to be able to
compile by month. So when month is equal to 200501 I want the value for the
contract to be returned.

This is what I have written:

=IF(('DOC TOTALS'!C1=200501),VLOOKUP(E2,'DOC TOTALS'!A:C,2,FALSE),0)

But this only will search the C1 row to see if it is =200501, I want the
whole column to be searched.

Thank you
Bradley

[Spencer Hutton]

Why not try a SUMIF function or COUNTIF function.


"Bradley" wrote:

> I am trying to write and IF statement with a VLOOKUP. I only want the value
> to be returned when the IF column is equal to a set value.
>
> For example, I have a data worksheet which has months and document #'s and
> total hits. I am creating another worksheet that wil search the data page
> and return the number of hits for the document, but I want to be able to
> compile by month. So when month is equal to 200501 I want the value for the
> contract to be returned.
>
> This is what I have written:
>
> =IF(('DOC TOTALS'!C1=200501),VLOOKUP(E2,'DOC TOTALS'!A:C,2,FALSE),0)
>
> But this only will search the C1 row to see if it is =200501, I want the
> whole column to be searched.
>
> Thank you
> Bradley

[Roger Govier]

Hi Bradley

Try
=IF(COUNTIF('DOC TOTALS'!C:C,"=200501")>0,VLOOKUP(E2,'DOC
TOTALS'!A:C,2,FALSE),0)

Regards

Roger Govier


Bradley wrote:
> I am trying to write and IF statement with a VLOOKUP. I only want the value
> to be returned when the IF column is equal to a set value.
>
> For example, I have a data worksheet which has months and document #'s and
> total hits. I am creating another worksheet that wil search the data page
> and return the number of hits for the document, but I want to be able to
> compile by month. So when month is equal to 200501 I want the value for the
> contract to be returned.
>
> This is what I have written:
>
> =IF(('DOC TOTALS'!C1=200501),VLOOKUP(E2,'DOC TOTALS'!A:C,2,FALSE),0)
>
> But this only will search the C1 row to see if it is =200501, I want the
> whole column to be searched.
>
> Thank you
> Bradley

[Richard Buttrey]

On Tue, 22 Nov 2005 08:59:07 -0800, "Bradley"
<Bradley@discussions.microsoft.com> wrote:

>I am trying to write and IF statement with a VLOOKUP. I only want the value
>to be returned when the IF column is equal to a set value.
>
>For example, I have a data worksheet which has months and document #'s and
>total hits. I am creating another worksheet that wil search the data page
>and return the number of hits for the document, but I want to be able to
>compile by month. So when month is equal to 200501 I want the value for the
>contract to be returned.
>
>This is what I have written:
>
>=IF(('DOC TOTALS'!C1=200501),VLOOKUP(E2,'DOC TOTALS'!A:C,2,FALSE),0)
>
>But this only will search the C1 row to see if it is =200501, I want the
>whole column to be searched.
>
>Thank you
>Bradley

=IF(MATCH(200501,'DOC TOTALS'!C:C),VLOOKUP(E2,'DOC
TOTALS'!A:C,2,FALSE),0)

HTH


Richard Buttrey
__