| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup/multiple
This is a small snapshot of what i have in 2 sheets:-
Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Hi!
Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
That's an array formula.
It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Hi,
I want the Names returned in rows like....... dept2 sales place1............name2 name7 name9 I cant give the end user the option of filtering for each instance of an item since the sheets have 1000s of records. Meanwhile, I will try to work with the formula, Thanks "Biff" wrote: Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Ok, just post back if you get stuck.
An error trap will make that formula kind of "long" (almost twice as long). Biff "Minerva" wrote in message ... Hi, I want the Names returned in rows like....... dept2 sales place1............name2 name7 name9 I cant give the end user the option of filtering for each instance of an item since the sheets have 1000s of records. Meanwhile, I will try to work with the formula, Thanks "Biff" wrote: Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Thanks a lot Biff......otherwise i had ended up creating 2 pivots with names
showing in the other! However, looks like i need to change the last parameter for each combination of the 3(department/category/place)...i.e........ROWS($1:1) because when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. Thanks anyways.......it helped me ease my work to some extent. -------------------------------------------------------------------------- "Biff" wrote: That's an array formula. It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
when i drag this to the next combination, i need to change this from
ROWS($1:4) back to ROWS($1:1) in this example case. That's why you should (if you're able) have the names returned across a row rather than down a column: name2..........name7..........name9 Biff "Minerva" wrote in message ... Thanks a lot Biff......otherwise i had ended up creating 2 pivots with names showing in the other! However, looks like i need to change the last parameter for each combination of the 3(department/category/place)...i.e........ROWS($1:1) because when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. Thanks anyways.......it helped me ease my work to some extent. -------------------------------------------------------------------------- "Biff" wrote: That's an array formula. It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
To return across the row:
=INDEX(Sheet2!$D$2:$D$11,SMALL(IF((Sheet2!$A$2:$A$ 11=$A2)*(Sheet2!$B$2:$B$11=$B2)*(Sheet2!$C$2:$C$11 =$C2),ROW(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),COLUMNS($A:A))) Biff "Biff" wrote in message ... when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. That's why you should (if you're able) have the names returned across a row rather than down a column: name2..........name7..........name9 Biff "Minerva" wrote in message ... Thanks a lot Biff......otherwise i had ended up creating 2 pivots with names showing in the other! However, looks like i need to change the last parameter for each combination of the 3(department/category/place)...i.e........ROWS($1:1) because when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. Thanks anyways.......it helped me ease my work to some extent. -------------------------------------------------------------------------- "Biff" wrote: That's an array formula. It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2:D$11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2:D$11)-ROW(Sheet2!D$2)+1),ROWS($1:1))) Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Here's a quick sample, implemented with a slight tweak to Biff's array to
enable copy across (horizontally) to pull the multiple names out, and ... with an error trap slapped on to return neat blanks: "" instead of #NUM! errors: http://cjoint.com/?crigMaFlaD Minerva_wks_1.xls (Just copy across from D2 in sheet: X by the smallest possible extent large enough to cater for the max number of names expected per combo. Admit I did shorten the formulas a little by using sheetnames: X, Y instead of: Sheet1, Sheet2 <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
I did shorten the formulas a little by using
sheetnames: X, Y instead of: Sheet1, Sheet2 <g) Those should be mandatory sheet names! Biff "Max" wrote in message ... Here's a quick sample, implemented with a slight tweak to Biff's array to enable copy across (horizontally) to pull the multiple names out, and ... with an error trap slapped on to return neat blanks: "" instead of #NUM! errors: http://cjoint.com/?crigMaFlaD Minerva_wks_1.xls (Just copy across from D2 in sheet: X by the smallest possible extent large enough to cater for the max number of names expected per combo. Admit I did shorten the formulas a little by using sheetnames: X, Y instead of: Sheet1, Sheet2 <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
"Biff" wrote:
I did shorten the formulas a little by using sheetnames: X, Y instead of: Sheet1, Sheet2 <g) Those should be mandatory sheet names! Ahh, those are default sheetnames but we can always be creative <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Appreciate all your help
Thanks. "Max" wrote: "Biff" wrote: I did shorten the formulas a little by using sheetnames: X, Y instead of: Sheet1, Sheet2 <g) Those should be mandatory sheet names! Ahh, those are default sheetnames but we can always be creative <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
You're welcome (from us) !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Minerva" wrote in message ... Appreciate all your help Thanks. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
I have a similar situation
i have one tab labeled "Schedule" that has a list of names in column E3:E10000, a list of cities in column F3:F10000, a list of states in column G3:G10000, and a list of numbers in column C3:C10000 Then on another tab labeled "Calculations" i have each city listed once in column C3:C114, followed by its state in column D3:D114, and a number in cell A1 I want the Calculations tab to find all names with that number in A1, in the city and state on the row it is on, and list them horizontally in the same row from columns G:P (max of 10 names) I edited the formulas above to look like this: INDEX(Schedule!$E$3:$E$10000,SMALL(IF((Schedule!$C $3:$C$10000=$A$1)*(Schedule!$F$3:$F$10000=$C4)*(Sc hedule!$G$3:$G$10000=$D4),ROW(Schedule!E$3:E$10000 )-ROW(Schedule!E$3)+1),COLUMNS($A:A))) I entered it as an array formulas and expanded the array over to column P and down to row 114 and entered as array again ... but populates the same name in every cell in that range (the first name it found) Any help on what I am doing wrong? "Max" wrote: You're welcome (from us) ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Minerva" wrote in message ... Appreciate all your help Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
