2SweetPete
New Member
- Joined
- Jul 30, 2012
- Messages
- 4
Hello all.
I have a 2007 workbook with two sheets. Sheet2 contains a Payee Name, Vendor ID (unique) and Address (150 rows). Sheet1 contains the same headers, plus some extra data input fields. The structure of Sheet1 cannot be changed -- it's used for an upload. I have all of the Payee names in a range as well as the Vendor IDs.
Sheet2:
<tbody>
</tbody>
I have a drop down list on Sheet1 in Col B with all of the Payee names. I'd like to be able to select a company from the list and have the Vendor ID populate in the cell to the left, and the address to the right. I can accomplish this with VLOOKUP, but it won't work for duplicate values. I've tried to use INDEX MATCH, but I also can't get it to work for duplicates. If you choose the second CIGNA from the list, the ID and Address remain the same.
Sheet1:
<tbody>
</tbody>
Does anyone know of a way I can get this to work without changing the names of the payees (CIGNA, CIGNA2)? I've looked at array formulas, but nothing I've tried has worked. I always seem to get foiled by the duplicates.
Thanks everyone!!
I have a 2007 workbook with two sheets. Sheet2 contains a Payee Name, Vendor ID (unique) and Address (150 rows). Sheet1 contains the same headers, plus some extra data input fields. The structure of Sheet1 cannot be changed -- it's used for an upload. I have all of the Payee names in a range as well as the Vendor IDs.
Sheet2:
A | B | C | |
1 | PAYEE | ID | ADDRESS |
2 | CIGNA | 43 | PO BOX 120 |
3 | CIGNA | 405 | 1150 WEST PERSHING RD |
4 | CINCINNATI INSURANCE COMPANY | 389 | PO BOX 2020 |
5 | CONSECO | 41 | 186 US HIGHWAY 51 |
<tbody>
</tbody>
I have a drop down list on Sheet1 in Col B with all of the Payee names. I'd like to be able to select a company from the list and have the Vendor ID populate in the cell to the left, and the address to the right. I can accomplish this with VLOOKUP, but it won't work for duplicate values. I've tried to use INDEX MATCH, but I also can't get it to work for duplicates. If you choose the second CIGNA from the list, the ID and Address remain the same.
Sheet1:
A | B | C | |
1 | ID | PAYEE | ADDRESS |
2 | 43 | CIGNA | PO BOX 120 |
3 | 43 | CIGNA | PO BOX 120 |
<tbody>
</tbody>
Does anyone know of a way I can get this to work without changing the names of the payees (CIGNA, CIGNA2)? I've looked at array formulas, but nothing I've tried has worked. I always seem to get foiled by the duplicates.
Thanks everyone!!
Last edited: