Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I lookup when match has more than one value?
Hi,
I have a Question.. is VLOOKUP plus IF possible? This is my fomula.. =VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), .... is my logical tests or conditions correct? and how will i put the VLOOKUP codes? Can you help me with this.. THank you so much "T. Valko" wrote: Well, then all you need to do is test for the presence of those 2 values that correspond to "efg". I have a feeling that your sample data is "fake" so any formula I suggest might not work on your real data since the formula is based on your explanation. Anyhow, try this: =IF(COUNTIF(A$2:A$20,"efg"),IF(AND(COUNTIF(B$2:B$2 0,567),COUNTIF(B$2:B$20,789)),INDEX({567,789},ROWS ($1:1)),IF(ROWS($1:1)=1,VLOOKUP("efg",A$2:B$20,2,0 ),"")),"") Copy to a total of 2 cells. -- Biff Microsoft Excel MVP "bonot1" wrote in message ... Thanks. I am working with your suggestion, but I am not sure if I expressed my problem clearly. Using your example, in A2:A20 there would be say three different values "abc", "cde", and "efg". When I lookup in B2:B20, there is a "123" for every "abc", a "345" for every "cde"; VLOOKUP works fine for these. However, rows with "efg" in column A sometimes have "789" in column B and sometimes have "567". What I need is to 1) be made aware that "efg" has two different matches in column B, and 2) know what the values of those two matches are. This is what I would like to automate. "T. Valko" wrote: Here's one way: Assume data in A2:B20. You want to extract data from column B that corresponds to a lookup_value. D2 = lookup_value Array entered** : =IF(ROWS($1:1)<=COUNTIF(A$2:A$20,D$2),INDEX(B$2:B$ 20,SMALL(IF(A$2:A$20=D$2,ROW(B$2:B$20)-MIN(ROW(B$2:B$20))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "bonot1" wrote in message ... Data is in random order, and the data to be returned is text. "T. Valko" wrote: Is the data sorted so that the lookup_values are grouped together or is the data random? Is the data to be returned text or numeric? -- Biff Microsoft Excel MVP "bonot1" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match and Lookup | Excel Worksheet Functions | |||
using MATCH, and LOOKUP | Excel Worksheet Functions | |||
match, lookup ??? | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Lookup or Match | Excel Worksheet Functions |