Lookup/multiple

[Guest]

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.

 

[Biff]

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$11,SMALL(IF((Sheet2!A$2:A$11=A$2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),ROW(Sheet2!D$2$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

 

[Biff]

That's an array formula.

It MUST be entered using the key combo of CTRL,SHIFT,ENTER.

Biff

 

[Guest]

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]

Ok, just post back if you get stuck.

An error trap will make that formula kind of "long" (almost twice as long).

Biff

 

[Guest]

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]

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

 

[Biff]

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$11)-ROW(Sheet2!D$2)+1),COLUMNS($A:A)))

Biff

 

[Max]

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>)

---

 

[Biff]

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]

Those should be mandatory sheet names!

Ahh, those are default sheetnames
but we can always be creative <g>

---

 

[Guest]

Appreciate all your help
Thanks.

Ahh, those are default sheetnames
but we can always be creative <g>

 

[Max]

You're welcome (from us) !

 

[richpauly13]

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 D3114, 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 (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)*(Schedule!$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?