Solution to search for value

[George]

Hi,

I am looking for a way to use the VLOOKUP function in a way that it
searches for a value in either column A or B (depending on a certain
expression), and returns the corresponding value in column D. So far
so good, I already found that out. But now also the value in column C
has to match.

Example:

Sheet A

A B C D
0 1 4
3 0 6

I need to fill in column D in Sheet A using the VLOOKUP function,
which searches in the following data on Sheet B:

Sheet B

A B C D
0 1 4 8
0 1 6 9
3 0 4 13
3 0 6 14

Sheet A!D, first row searches for a cell where B=1 (A=0 so I take the
value of B), AND where C=4 in Sheet B, so that would be 8.

Sheet A!D, second row searches for a cell where A=3 (A<>0 so I take
the value of A), AND where C=6 in Sheet B, so that would be 14.

George

 

[Frank Kabel]

Hi George

first have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm
(search for double lookup)

for your specific problem use the following
=IF(A=0,INDEX('sheet b'!$D$1:$D$99,MATCH(1,('sheet
b'!$B$1:$B$99=B1)*('sheet b'!$C$1:$C$99=C1),0)),[fill in if A<>0])

HTH
Frank