Excel - Dynamic Lookup to populate a cell 1

[NJko4]

Have departmental Excel worksheet that have 4 columns; Name, Phone No., Supplies that I have, and Supplies that I need, and Comments. Note, there are 75 rows that correspond to each of the 75 employees in the department.

Is it possible to have the "Comments" field populated with the name and phone number of any of the 74 other departmental employees who have what I need.

For example, if Jane enters "pencils" in the column titled "Supplies that I have" and Laura enters "No. 2 pencils" in the column titled "Supplies that I need" then I would like the comment column for Laura to display "Jane" and Jane's phone number.

Is this possible? Or, is there another method/suggestion to accomplish the desired objective?

Thanks in advance.

 

[AnotherHiggins]

If your table is in A1:E76 (I'm assuming Row 1 is used for headers), then paste this formula into cell E2:
[tab][COLOR=blue white]=IF(ISBLANK(D2), "", INDEX($A$2:$A$76, MATCH("*"&D2&"*", $C$2:$C$76, 0)) & " - " & INDEX($B$2:$B$76, MATCH("*"&D2&"*", $C$2:$C$76, 0)))[/color]

I'd suggest that you use Dynamic Named Ranges instead of range references like "A2:A76" to make the spreadsheet more flexible if you hire additional employees. Search this forum for Dynamic Named Ranges. Feel free to ask if you have any questions about setting them up.

Note: You asked for "pencils" to find "No. 2 pencils". So I included the asterisks which are wildcards. They cause the formula looks for the first cell in the "Supplies that I have" column that contains the string of letters entered into the "Supplies that I need" column. That means that if Jane puts in that she needs a "pen," it will return Lauras information because she has "No. 2 [!]PEN[/!]cils".

You'll have to decide if you want to deal with this type of ambiguity or if you want to only look for exact matches. One option would be to create drop-downs where you let users pick from a pre-defined list of supplies. That way, you don't even need to worry about misspellings.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[IngDep]

Initially, I considered the use of combo boxes.

However, I felt they would be quite cumbersome considering that I would have 150 combo boxes. Another challenge - how to copy a combo box down 74 rows!

Will try the suggestion and post back.