Try something this:

=SUMPRODUCT(--(E$2:E$10=A2),--(F$2:F$10=B2),G$2:G$10)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Timo" wrote in message
...
Hi Valko,

I have something similar and I am just used to vlookup for one single
value
to compare two columns so I get value in column2 returned.

Now I do have two columns plus a condition and three columns, and I would
like to get the value in column3 returned.

example:

ID name shs test results ID name shs

1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10


I thought vlookup can help if (A2+A3) match (E2+F2), then return the value
in column G.

Result: Cell D2 should get value "10" (G3) and Cell D3 should get the
value
"20" (G2).

Thank you so much in advance.

Regards, Timo


"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Actually the data that i want to be returned is both numeric and text
thats
why i had IF function.. in a typical VLOOKUP formula is should be like
this..

=VLOOKUP(B1,Data!A1:A1000,2,FALSE)

but since i only want to have the data that is equivalent to the date
and
the name on the database thats why im to use this formula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

that is also why im having problems in how to formulate the conditions
in
the IF function and how to get the data using the VLOOKUP.

I hope you can help me.

Thank you so much.




"T. Valko" wrote:

Where is the data you want returned?

What is the data type of the value to be returned? Is it text?
Numeric?

When there are multiple lookup_values you would typically use an array
formula** like this:

=INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data! B1:B10=D1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi,

I have a Question.. if VLOOKUP plus IF possible? This is my fomula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

Can you help me with this..

THank you.


"David Hilberg" wrote:

=IF( CountIf(a1:a10,"Joe")<1, CountIf(a1:a10,"Joe"),
LookUp(..etc...) )

will give you the count if there are more or fewer than one.
Otherwise, it performs the lookup.

- David

On Jul 23, 9:12 pm, bonot1
wrote:
I am using LOOKUP functions to retrieve info from a list. Some
of
the
lookup
values have more than one match in the list. Is there a function
that
allows
me to retrieve multiple elements for one lookup value, or at
least a
function
that tells me there are duplicate matches?