Using INDEX/MATCH with duplicate values

[JannetteChristie]

I receive the following data:

Book1
	A	B	C	D	E	F	G	H
1	Code	Config	Flow rate	Flor rate/pump	Head	Head unit	Qty	Selling price
2	code 1	D/A	21.5	21.5	7	bar	1	24681.00000
3	code 2			0			1	25061.54000
4	code 3	D/A	10	10	6.5	bar	1	15857.00000
5	code 2			0			1	13583.10000
6	code 4			0			1	685.00000
Sheet1

However when I try to list the codes out on a seperate sheet with the prices I get the same price for code 2 not the different prices (25061.54 and 13583.10) - how do I overcome this please ?

 

[JackDanIce]

Hi,

1 What is your formula and why are you using INDEX+MATCH over VLOOKUP?
2 One suggestion is insert a column after A and enter formula = "=A2&"."&COUNTIF($A$2:A2)" and drag to the end. Then you can use VLOOKUP against this new column for a unique value per row to return specific price. VLOOKUP formula would be similar to:

=VLOOKUP(A2&"."&COUNTIF($A$2:A2),Range(B2:H6),7,0)

 

[Aladin Akyurek]

What is the layout of your separate sheet?

 

[JannetteChristie]

Hi,

I am using the following formula: =IFERROR(INDEX('Input Sheet'!$D$11:$AJ110,MATCH($AR2,Input_Sheet__QuotationItemDTO.Code,0),10),"")

When I use your formaula as so: =VLOOKUP(Ar2&"."&COUNTIF($Ar$2:Ar2),Range(at2:bb11),9,0) - get the error message too few arguments

 

[JannetteChristie]

The seperate sheet should look like the following:

Book1
	A	B	C	D	E	F	G	H
1	Stock/Service Code	Configuration	Flow rate	Flow Rate/Pump (l/s)	Head	Duty Head Unit	Quantity	Selling Unit Price
2	code 1	D/A	21.5				1	24681
3	extra code 1						1
4	extra code 2						2
5	code 2	0	0				1	25061.54
6	code 3	D/A	10				1	15857
7	extra code 1						1
8	extra code 2						1
9	code 2	0	0				1	13583.1
10	code 4	0	0				1	685
Sheet2

 

[JackDanIce]

Try changing

COUNTIF($Ar$2:Ar2, AR2)

 

[Aladin Akyurek]

In H2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$H$2:$H$6,SMALL(IF(Sheet1!$A$2:$A$6=$A2,ROW(Sheet1!$A$2:$H$6)-ROW(INDEX(Sheet1!$A$2:$H$6,1,1))+1),COUNTIFS($A$2:A2,A2))),"")