combining VLOOKUP and IF

[z.entropic]

I'm trying to get around the problem of VLOOKUP looking up only the first
found match. Here's and example:

Each row in the first worksheet has a unique value in the first column
(file_ID), and a variable number of the same file_ID entries in consecutive
row blocks in the second worksheet. Now, I'd like to extract to the first
worksheet the corresponding values in, let's say, column 5 in the second
worksheet IF the column 3 value in that 2nd worksheet meets a certain
condition. The values in column 3 are unique for each file_ID entry.

I couldnt solve the problem with various combinations of VLOOKUP and IF and
suspect that an array formula would be needed--but I don't know how to bite
it...

z.entropic

 

[Bob Phillips]

In E1: =SUMPRODUCT(--(Sheet2!E1:E1000=the_val),--(Sheet2!A1:A1000=A1))

not really sure about the req so it is a bit of a guess.

 

[z.entropic]

Thanks, but I'm not sure if I follow your advice and formula. Here's a
clearer example and description of the problem:

For matching values a, b, c in Sheet_1!A and Sheet_2!A, copy into Sheet1_!B
the values in Sheet_2!C only IF Sheet_2!B=2.

columns
A B C
--------start_sheet_1-----------
a X
b X
c X
--------end_sheet_1-----------

--------start_sheet_2-----------
a 1 x
a 2 x
a 3 x

b 1 x
b 2 x
b 3 x

c 1 x
c 2 x
c 3 x
--------end_sheet_2-----------

 

[Ola]

Try this Array formula:
=INDEX($C$1:$C$100,MATCH(A1&"2",(Sheet_2!$A$1:$A$100&Sheet_2!$B$1:$B$100),0))

Ola Sandstrom

Note:
Confirm the formula by holding down Ctrl and Shift, and then press Enter.
Othervise the result will be #VALUE!
If there is no match the formula will return #N/A. Then use
IF(ISERROR(...),"",...)

 

[Bob Phillips]

That should probably be

=INDEX(Sheet_2!$C$1:$C$100,MATCH(A1&"2",(Sheet_2!$A$1:$A$100&Sheet_2!$B$1:$B
$100),0))