i still dont understand the 1 and the 0?...

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

The 1 is the lookup_value and the 0 means we want to find an exact match of
the lookup_value.

Based on the sample I posted...

(A1:A5=E1)*(B1:B5=F1)

When both conditions are TRUE the result will be 1.

(Bob=Sue)*(x=a) = 0
(Sue=Sue)*(x=a) = 0
(Bob=Sue)*(z=a) = 0
(Sue=Sue)*(a=a) = 1
(Tom=Sue)*(h=a) = 0

This array of 1s and 0s make up the lookup_array.

MATCH(1,{0;0;0;1;0},0)

The result of MATCH is 4 and is passed to the INDEX function meaning we want
the 4th element of the indexed range C1:C5.

=INDEX(C1:C5,4)

=INDEX({"AA";"BB";"CC";"GG";"FF"},4)

GG is the 4th element of the indexed range so the result of the formula is
GG


E1 = Sue
F1 = a

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

=GG


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?...

Thank you.



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