How can I lookup when match has more than one value?
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?
|