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 |
#52
|
|||
|
|||
How can I lookup when match has more than one value?
I am having trouble with the following:
I have a sheet sorted in ID order and they have reported months next to them (therefore there are duplicates ID). Data source 2138 january 147 2138 february 161 2138 may 112 2138 june 191 2384 january 118 2384 february 119 New report January february march april 2138 2384 I want to find a fuction where I need the 3rd column from source with ID and Month matching. Can you please help? "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? |
#53
|
|||
|
|||
How can I lookup when match has more than one value?
Hi I actually have another similar problem, can you help me?
Hi I actually have a similar problem. Could you help me: Basically. I want to look up number 222222 in column A, and have it returns 3 values from column B which are 666666, 777777, and 9999999 in 3 separate column. Please help! 1....................A....................B 2..............123456...............1111111 3..............222222...............6666666 4..............222222...............7777777 5..............222222...............9999999 6..............444444...............8888888 |
#54
|
|||
|
|||
How can I lookup when match has more than one value?
Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "G." wrote in message ... Hi I actually have another similar problem, can you help me? Hi I actually have a similar problem. Could you help me: Basically. I want to look up number 222222 in column A, and have it returns 3 values from column B which are 666666, 777777, and 9999999 in 3 separate column. Please help! 1....................A....................B 2..............123456...............1111111 3..............222222...............6666666 4..............222222...............7777777 5..............222222...............9999999 6..............444444...............8888888 |
#55
|
|||
|
|||
How can I lookup when match has more than one value?
Hi, I have used Pivot table and it only shows those 3 values in vertical way (1 column)..but i want the values to be shown horizontally (3 columns). Do you know if Pivot table can do that? Thanks, G. "ozgrid.com" wrote: Use a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "G." wrote in message ... Hi I actually have another similar problem, can you help me? Hi I actually have a similar problem. Could you help me: Basically. I want to look up number 222222 in column A, and have it returns 3 values from column B which are 666666, 777777, and 9999999 in 3 separate column. Please help! 1....................A....................B 2..............123456...............1111111 3..............222222...............6666666 4..............222222...............7777777 5..............222222...............9999999 6..............444444...............8888888 . |
#56
|
|||
|
|||
How can I lookup when match has more than one value?
Hi
Drag B to the Column area -- Regards Roger Govier G. wrote: Hi, I have used Pivot table and it only shows those 3 values in vertical way (1 column)..but i want the values to be shown horizontally (3 columns). Do you know if Pivot table can do that? Thanks, G. "ozgrid.com" wrote: Use a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "G." wrote in message ... Hi I actually have another similar problem, can you help me? Hi I actually have a similar problem. Could you help me: Basically. I want to look up number 222222 in column A, and have it returns 3 values from column B which are 666666, 777777, and 9999999 in 3 separate column. Please help! 1....................A....................B 2..............123456...............1111111 3..............222222...............6666666 4..............222222...............7777777 5..............222222...............9999999 6..............444444...............8888888 . |
Thread Tools | |
Display Modes | |
|
|