ExcelBanter - IF and Vlookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - IF and Vlookup (https://www.excelbanter.com/excel-discussion-misc-queries/201725-if-vlookup.html)

How do I lookup the value in column B for all (03), then lookup the value in
column C (i.e. 30), then return the value in column D.

For example here is what I want to examine:

in another worksheet, If cell B4, then look at value in C10,
then lookup the value in C10 in table_array C:D
and return the value in the second column an exact match

B C D
SITE# SACS FUND# FUND#
03 30 400
02 53 402
03 56 403
02 51 430
08 51 433

The column that is in ascending order is D. Please help.

Here is what I came up with but its returned a #VALUE
=IF(B4,IF(C10,(VLOOKUP(C10,Reference!$C:$D,2,FALSE ))))

On Mon, 8 Sep 2008 10:52:02 -0700, Olivia
wrote:

How do I lookup the value in column B for all (03), then lookup the value in
column C (i.e. 30), then return the value in column D.

For example here is what I want to examine:

in another worksheet, If cell B4, then look at value in C10,
then lookup the value in C10 in table_array C:D
and return the value in the second column an exact match

B C D
SITE# SACS FUND# FUND#
03 30 400
02 53 402
03 56 403
02 51 430
08 51 433

The column that is in ascending order is D. Please help.

Here is what I came up with but its returned a #VALUE
=IF(B4,IF(C10,(VLOOKUP(C10,Reference!$C:$D,2,FALS E))))

Try this formula:

=SUMPRODUCT((B2:B100="03")*(C2:C100="30"),D2:D100)

If the data in column B and C is not text, remove the " "

Hope this helps / Lars-Åke