• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested function for searching and populating existing data on a table

R

rsomist

Member
Hello!


I'm trying to figure out a function that will allow me to enter in a unique identifier, and if that identifier is already on the table, it will take certain corresponding cells and populate them into the table for me.


Basically, what I have is a table that has a list of employee names (unique identifier), their departments, their employee numbers, and the classes they're enrolled to take. These tables are thousands of rows long and hold the database to all employee's class participation. Because it is just a running roster, many times an employee's name will appear more than once. What I'm trying to eliminate is the tedious work of logging into our system to figure out what department the employee works in, their position, and employee number when it's probably already elsewhere on the table. I would like a function that recognizes the employee name elsewhere on the table (if they are previously listed) and populates their department, position, and employee number and if they're not previously listed, then the cell stays blank. I've been playing around with combinations of the IFERROR, VLOOKUP, INDEX and MATCH functions trying to make this work with no luck. In my example, I would be manually entering data in columns A, D, E (unique identifier), G, H, I, and J. When I enter the employee's name into column E, I would like the function to search the table for that name and populate the corresponding cells in columns B, C, and F.


Thank you in advance!


Amber

upload_2015-8-11_10-47-35.png
 
Hi, and welcome to the forum! :awesome:

Formula in B2:
=IF(COUNTIF($E$1:$E1,[@TRAINEE NAME])=0,"",
INDEX(B:B,MATCH([@TRAINEE NAME],$E$1:$E1,0)))

Or, if you first put the formula in a later row, adjust all the numbers in formula above to be that cells row number -1. Copy to the other columns as needed. Formula stays blank if this is first entry. Overwrite the formula to fill in information.
 
Hi Luke M! Thanks so much for responding. I'm still having a little trouble here and I don't know if it's because my starting location. I'm actually wanting to start this function in cell B9. In my existing worksheet, all cells above my starting row (so in this example, rows 2-8) have the information entered in and won't need to run off of a function. It's the rows under these that will need the information populated based on the information that's already in the worksheet and the identification of the TRAINEE NAME elsewhere on the sheet (rows 2-8). So ideally, I would want to be able to type in the name "MICKEY MOUSE" in cell E9 and since "MICKEY MOUSE" is already on that spreadsheet, cell B9 would say "CALL CENTER," cell C9 would say "CSR," and cell F9 would say "B165." If I were to enter in the name "ALVIN CHIPMUNK" into cell E10, then nothing would populate in cells B10, C10, or F10 because "ALVIN CHIPMUNK" isn't on the sheet already. I'll attach a pic of the error I currently get. Thanks again so much for your help!

upload_2015-8-11_13-19-28.png
 
Before you put in a formula that uses structural reference, need to extend the table first. Before putting in formula then, type something (anything you want), into cell E9. Then the table will extend to row 9 and you can put in the formula.

To adjust for formula in in row 9, should be:
=IF(COUNTIF($E$1:$E8,[@TRAINEE NAME])=0,"",
INDEX(B:B,MATCH([@TRAINEE NAME],$E$1:$E8,0)))
 
You must log in or register to reply here.
Back
Top