Filling in a field based on values in another field in same row

I'm new at this, so perhaps I should have posted this in the new users section.

Using Excel 2003. I have a report which ranks some managers of various regions in our company, listed in order according to some numbers reflecting their performance. This report is given to me in a txt file, in columns. I have no problem importing this data into my spreadsheet.

However, the data I am bringing into the spreadsheet doesn't contain the managers' names. It only has their region and job title. There are 3 possible titles, and about 20 regions.

I have another spreadsheet which lists the managers. The names could change from time to time, but it's usually pretty static. I'd like to find a way to make my first spreadsheet contain the names.

Thing is, because this spreadsheet ranks the managers, the order of the rows is different each time. So I can't just copy-paste the Firstname and Lastname fields. I need some way for each row to get the value of the region and job title, go to the other spreadsheet and determine the manager's name based on those values, and populate the firstname and lastname columns with the correct values.

If this can be done automatically (that is, when the row is populated with the numbers and region/title information), great. But if I have to add a pushbutton to make it populate it, that's not a problem, either.

One more thing regarding the list of names which is currently in a separate spreadsheet: I have no problem making it into a txt file, csv file, or even Access database with just the one table. I have no problem making it a seperate sheet in the same Excel file.

thanks in advance

Can you post a sample workbook showing what you've got... you can replace the names etc with fictional names...

Try:

In F6:

=INDEX(Supv!E$2:E$61,MATCH(1,(Supv!$B$2:$B$61=$B6)*(Supv!$C$2:$C$61=$C6),0))

adjust the ranges to suit your actual data in the Supv! sheet.

Then confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula... then you can copy it over to the next column and then copy down the columns.

Originally Posted by middletree

It gave me an "N/A' error.

I have just discovered the VLOOKUP function, so I will play with it. Thanks

Did you confirm the formula with CTRL+SHIFT+ENTER?

go to the cell and hit F2... then hold the CTRL+SHIFT and press ENTER.