combining VLOOKUP and IF
 

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

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.

--
HTH

Bob Phillips

"z.entropic" wrote in message
...
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

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-----------


"Bob Phillips" wrote:

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.

--
HTH

Bob Phillips

"z.entropic" wrote in message
...
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

Try this Array formula:
=INDEX($C$1:$C$100,MATCH(A1&"2",(Sheet_2!$A$1:$A$1 00&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(...),"",...)

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))

--
HTH

Bob Phillips

"Ola" wrote in message
...
Try this Array formula:

=INDEX($C$1:$C$100,MATCH(A1&"2",(Sheet_2!$A$1:$A$1 00&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(...),"",...)