Match and concatenate function

[songo]

Hello guys,
I am wondering whether it’s possible to use MATCH & CONCATENATE function together.
Have to tables:

TABLE1:

Item	Price type
600314095	I1
600314095	I2
600314095	I3
600314149	I1
600314149	I2
600314149	I3
600345655	I1
600345655	I2
600345655	I3
600383015	I1
600383015	I2
600383015	I3

<tbody>
</tbody>

TABLE2:

Item	Price type	ROW of table 1
600314095	I2	Match+ concatenate?
600314149	I2
600345655	I2
600383015	I2

<tbody>
</tbody>

Is it possible to use function as below:
=MATCH(CONCATENATE(TABLE2!Item;TABLE2!Price type);CONCATENATE(TABLE1!Item; TABLE1!Price type);0)
In TABLE 2 I need to know I which row in TABLE1 is Item number with specific price type next to.
When I try to do it I receive: #VALUE! Error.

Just want you to know, that I can’t modify or filter TABLE 1, this is pure input data and need to stay like it is, I can do any changes in table 2.

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F
1	Item	Price type		Item	Price type	ROW of table 1
2	600314095	I1		600314095	I2	2
3	600314095	I2		600314149	I2	5
4	600314095	I3		600345655	I2	8
5	600314149	I1		600383015	I2	11
6	600314149	I2
7	600314149	I3
8	600345655	I1
9	600345655	I2
10	600345655	I3
11	600383015	I1
12	600383015	I2
13	600383015	I3
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(($A$2:$A$13=$D2)*($B$2:$B$13=$E2),ROW($B$2:$B$13)-(ROW($A$2)-ROW($A$1)))

 

[songo]

Thanks Alany, that's exactly what I needed.
Now i know that i can replace vlookup with index +sumproduct+row

By the way, if i change ranges to whole column, formula will be even shorter.
Like this:
=SUMPRODUCT((A:A=$D2)*(B:B=$E2),ROW(B:B)))

 

[AlanY]

you're welcome.

Use the whole column as range will slow down the spreadsheet as excel 2007 and above have 2^20 (over a million) rows to check

 

[songo]

that's a good point, i have noticed that by changing parameters it takes up to 2sek to calculate for my Excel 2016, now changed range to $A$1:$A$5000 and it works immediately