Index Match duplicate values

[2SweetPete]

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:

	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!!

 

[T. Valko]

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:

	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!!

Do you have the duplicates in your drop down list? If so, there's no way to distinguish which one you select even though they may be considered to be in "logical order".

I think your idea about using Cigna1 and Cigna2 is about as good as you'll be able to do.

 

[2SweetPete]

Do you have the duplicates in your drop down list? If so, there's no way to distinguish which one you select even though they may be considered to be in "logical order".

I think your idea about using Cigna1 and Cigna2 is about as good as you'll be able to do.

Yeah, the duplicates exist in my drop down. This information has to be uploaded into a different system, so the names of the Payees can't be changed (to CIGNA1,CIGNA2)... that's just the only way I've gotten it to work.

 

[Vidar]

Can you create a new column on sheet 2.
This formula should copy the names in column A, but duplicates will get 2, 3, 4 and so on after their names.
Then you can use that column in your Data Validation List.
=IF(COUNTIF(A$2:A2,A2)>1,A2&COUNTIF(A$2:A2,A2),A2)
Put this in the second row and copy down.

Vidar

 

[2SweetPete]

Can you create a new column on sheet 2.
This formula should copy the names in column A, but duplicates will get 2, 3, 4 and so on after their names.
Then you can use that column in your Data Validation List.
=IF(COUNTIF(A$2:A2,A2)>1,A2&COUNTIF(A$2:A2,A2),A2)
Put this in the second row and copy down.

Vidar

Unfortunately, I have to keep the Payee Name list correct. Is there any way that the data validation list could display CIGNA 1, CIGNA 2 and still retain the "value" of CIGNA? I don't think so without the use of another column for VLOOKUP.

 

[Vidar]

One solution is to insert a "Form Control" "Combo Box" instead of Data Validation. Set it "Input Range" property to column with Payee,
And "Cell Link" to somewhere else. The index number of the list item you choose from the Combo Box will show in that cell.
And with that index number you can use INDEX to get to the correct row.

Vidar

 

[AhoyNC]

Maybe this will help some.
This will show each duplicate payee (1 at a time) from a drop down list in B1.
Copy formulas in row 3 down as many rows that you need. It will show blank if there is no data.

These are array formulas and need to be entered with CTRL-SHIFT-ENTER

Sheet1

*	A	B	C	D
1	*	CIGNA	*	*
2	ID	PAYEE	ADDRESS	*
3	43	CIGNA	PO BOX 120	*
4	405	CIGNA	1150 WEST PERSHING RD	*
5	*	*	*	*
6	*	*	*	*
7	*	*	*	*
8	*	*	*	*

<TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula A3 {=IF(ROWS($A$3:A3)<=COUNTIF(Sheet2!$A$2:$A$5,$B$1),INDEX(Sheet2!$B$2:$B$5,SMALL(IF(Sheet2!$A$2:$A$5=$B$1,ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1),ROWS($A$3:A3))),"")} B3 {=IF(ROWS($A$3:A3)<=COUNTIF(Sheet2!$A$2:$A$5,$B$1),INDEX(Sheet2!$A$2:$A$5,SMALL(IF(Sheet2!$A$2:$A$5=$B$1,ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1),ROWS($A$3:A3))),"")} C3 {=IF(ROWS($A$3:A3)<=COUNTIF(Sheet2!$A$2:$A$5,$B$1),INDEX(Sheet2!$C$2:$C$5,SMALL(IF(Sheet2!$A$2:$A$5=$B$1,ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1),ROWS($A$3:A3))),"")} A4 {=IF(ROWS($A$3:A4)<=COUNTIF(Sheet2!$A$2:$A$5,$B$1),INDEX(Sheet2!$B$2:$B$5,SMALL(IF(Sheet2!$A$2:$A$5=$B$1,ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1),ROWS($A$3:A4))),"")} B4 {=IF(ROWS($A$3:A4)<=COUNTIF(Sheet2!$A$2:$A$5,$B$1),INDEX(Sheet2!$A$2:$A$5,SMALL(IF(Sheet2!$A$2:$A$5=$B$1,ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1),ROWS($A$3:A4))),"")} C4 {=IF(ROWS($A$3:A4)<=COUNTIF(Sheet2!$A$2:$A$5,$B$1),INDEX(Sheet2!$C$2:$C$5,SMALL(IF(Sheet2!$A$2:$A$5=$B$1,ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1),ROWS($A$3:A4))),"")} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 B1 List * =Sheet2!$A$2:$A$5 * <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sheet2

*	A	B	C	D
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	*
6	*	*	*	*

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[2SweetPete]

That's close. However, Sheet1 is for data entry. I need to input a different Payee on each line. Is there any way to store the duplicates within a list in A1? I found a VBA script that would work if I was able to get the duplicate nth value (1,2,3). I'm just sort of grasping at straws at this point.

The combo box was close as well... but it didn't store any actual value within the cell.

I appreciate everyone's responses.

 

[michaelsmith559]

This may be a little late but does this work:

Excel 2012
	A	B	C	D
1	ID	PAYEE	ADDRESS
2	43	CIGNA	PO BOX 120
3	405	CIGNA	1150 WEST PERSHING RD
4
5
Sheet1

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(Sheet2!$B$2:$B$5, SMALL(IF(B2=Sheet2!$A$2:$A$5, ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1), ROW(1:1))),"" )}
C2	{=IFERROR(INDEX(Sheet2!$C$2:$C$5, SMALL(IF(B2=Sheet2!$A$2:$A$5, ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1), ROW(1:1))),"" )}
Press CTRL+SHIFT+ENTER to enter array formulas.