Return ID's of account managers

[faisal786]

Hi,

I have a table with a list of account managers and their unique ID's. There are three other worksheets that contain a list of the manager's names and I want to replace them with their ID's taken from the account managers table.

I have used V-Lookup but I can only get it to increment instead of returning the respective ID of the account managers. Some of the account managers are repeated in the worksheets so I want their correct ID's instead of just 1,2,3,4,5,6,7... in the column.

Please find the attached file with the dummy data. The version is 97-2003. If you could advise me of the correct formula so I can find out how to do it that would be great!

[AliGW]

Welcome to the forum.

I do not see where you have mocked up what you want. Please add this manually for a few rows and post the workbook again once added. Thanks.

[faisal786]

Hi,

I have stated in the worksheets what I would like to do. I hope it helps.

[AliGW]

Ah, I see. Sorry, but you can't replace a cell value using a formula. You would need to use INDEX MATCH in an adjacent cell.

For example, in E2:

=INDEX('Account Managers'!$A$2:$A$149,MATCH(D2,'Account Managers'!$B$2:$B$149,0))

[faisal786]

Thank you, that was very helpful. Seems Index Matches are a bit more complicated than V-Lookups!

[AliGW]

No, thy aren't, and they allow a lookup from right to left.

=INDEX(return_value_array,MATCH(lookup_value,lookup_array,return_type))

[faisal786]

Ah. The syntax makes it more clear. Thanks!

[AliGW]

No problem.