Index match formula with condition of only returning first match

[willow1985]

I am needing help with an index match formula. The formula has 2 matches and returns one value, however I would like it if one out of the 2 matches only returned a value on the first match and left all other matches blank.
=IFERROR(INDEX(INV[Fee''s and Taxes],MATCH(1,([@line]=WO[[#Headers],[BARREL]])*([@[Inv Number]]=INV[Invoice '#]),0)),"") - The portion in Bold to only return the first match

See below spreadsheet example, any help would be greatly appreciated!

Example2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	Inv Number	line	Project Subtotal	BARREL	STILL		Invoice #	Fee's and Taxes		What I want results to look like:
2	11111	77125	BARREL	10.32	87.13			77125	87.13			BARREL	STILL
3	22222	77125	STILL	70		87.13		70452	15.38			87.13
4	33333	70452	STILL	75.24		15.38		95231	13.41				87.13
5	44444	70452	STILL	1.29		15.38							15.38
6	55555	70452	STILL	167.5		15.38
7	66666	70452	STILL	82.5		15.38
8	77777	95231	BARREL	229.32	13.41
9	88888	95231	STILL	495		13.41						13.41
10	99999	95231	STILL	434.5		13.41							13.41
11
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(INDEX(INV[Fee''s and Taxes],MATCH(1,([@line]=WO[[#Headers],[BARREL]])*([@[Inv Number]]=INV[Invoice '#]),0)),"")
F2:F10	F2	=IFERROR(INDEX(INV[Fee''s and Taxes],MATCH(1,([@line]=WO[[#Headers],[STILL]])*([@[Inv Number]]=INV[Invoice '#]),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[GraH]

Hi,

Maybe like this? You can drag the formula in [E2] to the right.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	Inv Number	line	Project Subtotal	BARREL	STILL		Invoice #	Fee's and Taxes		What I want results to look like:
2	11111	77125	BARREL	10.32	87,13			77125	87,13			BARREL	STILL
3	22222	77125	STILL	70		87,13		70452	15,38			87.13
4	33333	70452	STILL	75.24		15,38		95231	13,41				87.13
5	44444	70452	STILL	1.29									15.38
6	55555	70452	STILL	167.5
7	66666	70452	STILL	82.5
8	77777	95231	BARREL	229.32	13,41
9	88888	95231	STILL	495		13,41						13.41
10	99999	95231	STILL	434.5									13.41
11
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR((COUNTIFS(WO[[#Headers],[Inv Number]:[Inv Number]]:WO[@[Inv Number]:[Inv Number]],WO[@[Inv Number]:[Inv Number]],WO[[#Headers],[line]:[line]]:WO[@[line]:[line]],WO[[#Headers],[BARREL]])=1)*INDEX(INV[[Fee''s and Taxes]:[Fee''s and Taxes]],MATCH(1,(WO[@[line]:[line]]=WO[[#Headers],[BARREL]])*(WO[@[Inv Number]:[Inv Number]]=INV[[Invoice '#]:[Invoice '#]]),0)),"")
F2:F10	F2	=IFERROR((COUNTIFS(WO[[#Headers],[Inv Number]:[Inv Number]]:WO[@[Inv Number]:[Inv Number]],WO[@[Inv Number]:[Inv Number]],WO[[#Headers],[line]:[line]]:WO[@[line]:[line]],WO[[#Headers],[STILL]])=1)*INDEX(INV[[Fee''s and Taxes]:[Fee''s and Taxes]],MATCH(1,(WO[@[line]:[line]]=WO[[#Headers],[STILL]])*(WO[@[Inv Number]:[Inv Number]]=INV[[Invoice '#]:[Invoice '#]]),0)),"")

Using a custom format to "hide" the zeros:

#.##0.00 , , ""

 

[willow1985]

Hi,

Maybe like this? You can drag the formula in [E2] to the right.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	Inv Number	line	Project Subtotal	BARREL	STILL		Invoice #	Fee's and Taxes		What I want results to look like:
2	11111	77125	BARREL	10.32	87,13			77125	87,13			BARREL	STILL
3	22222	77125	STILL	70		87,13		70452	15,38			87.13
4	33333	70452	STILL	75.24		15,38		95231	13,41				87.13
5	44444	70452	STILL	1.29									15.38
6	55555	70452	STILL	167.5
7	66666	70452	STILL	82.5
8	77777	95231	BARREL	229.32	13,41
9	88888	95231	STILL	495		13,41						13.41
10	99999	95231	STILL	434.5									13.41
11
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR((COUNTIFS(WO[[#Headers],[Inv Number]:[Inv Number]]:WO[@[Inv Number]:[Inv Number]],WO[@[Inv Number]:[Inv Number]],WO[[#Headers],[line]:[line]]:WO[@[line]:[line]],WO[[#Headers],[BARREL]])=1)*INDEX(INV[[Fee''s and Taxes]:[Fee''s and Taxes]],MATCH(1,(WO[@[line]:[line]]=WO[[#Headers],[BARREL]])*(WO[@[Inv Number]:[Inv Number]]=INV[[Invoice '#]:[Invoice '#]]),0)),"")
F2:F10	F2	=IFERROR((COUNTIFS(WO[[#Headers],[Inv Number]:[Inv Number]]:WO[@[Inv Number]:[Inv Number]],WO[@[Inv Number]:[Inv Number]],WO[[#Headers],[line]:[line]]:WO[@[line]:[line]],WO[[#Headers],[STILL]])=1)*INDEX(INV[[Fee''s and Taxes]:[Fee''s and Taxes]],MATCH(1,(WO[@[line]:[line]]=WO[[#Headers],[STILL]])*(WO[@[Inv Number]:[Inv Number]]=INV[[Invoice '#]:[Invoice '#]]),0)),"")

Using a custom format to "hide" the zeros:

#.##0.00 , , ""

Works perfect! Thank you

 

[GraH]

Most welcome . Thank you for the feedback.