If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#7
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
Thread Tools | |
Display Modes | |
|
|