Match 3 cells pair with other 3 cells pair. Google Sheets

Question

I'm using Google Sheets for our production price calculation and we are getting new orders with different data every week.
I have all the price calculations sorted out but sometimes there are the same data in orders that already been in the past and I have to manually search for it and use the same price if it exists.

As you can see in the example above, when I enter in the selected cell data "100", I have to check if it already exists in cells above (all three cell in the same row) and if it does enter its price on the cell on the right("=" sign), if it doesn't it could say "new" or be left empty.
I looked at the INDEX and MATCH functions but they don't seem to do the trick.
Do you have any suggestions? Of course the formula should be easily copied to every next cell down when new data and orders come in.

Answer 1

Approach

In this case it's useful to have an index for your table. I created a simple one that concatenates the 3 values you have with the & operator. You can see in the table below for the complete formula. Just drag it down to the whole table to make it automatic.

For the price calculation then I am using a VLOOKUP. It will search for the index generated by the three values in the new row and get the corresponding Price if ti exists. If not, it will print NEW. That's just a placeholder of course, you can edit it as you want. I put the first cell of your table as absolute reference in the VLOOKUP formula so you can drag it down and it will always get its upper (already filled) portion.

Table

INDEX        X      Y     Z     Price
11010030     110    100   30    1
500300100    500    300   100   2.3
12030010     120    300   10    1.2
500300100    500    300   100   2.3
12030010     120    300   10    1.2
11010030     110    100   30    1
3004510      300    45    10    NEW
11010030     110    100   30    1
=B10&C10&D10                    =IFERROR(VLOOKUP(A10, $A$2:I9,5,0), "NEW")

Answer 2

Based on the correct initial thought by Alessandro, please use the following single arrayformula in cell E2

=ArrayFormula(IF(LEN(A2:A)>0, 
                  IF(LEN(D2:D)>0, 
                      VLOOKUP(A2:A&B2:B&C2:C, {A2:A&B2:B&C2:C,D2:D},2,0),"new"),""))  

The formula works as a helper column, only showing you what price to use in column D (if it previously exists) or lets you know that you need to calculate a new one.

Functions used:

• VLOOKUP
• ArrayFormula
• LEN
• IF