MULTIPLE LOOKUPS

Copper Contributor

hi I am getting my hair off with this one, I have 3 columns  

 

 

i need to type in the part number and the operation number (sheet1)and the PPH (sheet1) needs to find the correct pph on the second sheet based on the 2 factors

part number / operation / pph
1 / 10 / 1000
1 / 20 / 2000

sheet1sheet1sheet2sheet2

5 Replies
I would suggest adding a helper column to your second sheet which combined the part number and operation - e.g. =A1&"/"&B1.

Then you could make a lookup such as: =INDEX(PPh column, MATCH(part number & "/" & operation, new helper column, 0)).

@SaviaHave I done this correctly? all I get is #REF!

@Savia 

Hello @STUARTMAC,

 

That would be something like:

{=INDEX(PPH_Range, MATCH(1, (Part_Number=Part_Number_Range)*(Operation=Operation_Range), 0))}

 

Refer here:

https://exceljet.net/formula/index-and-match-with-multiple-criteria

 

With a sample file, I could help further.

Are your ranges the same size? That error indicates that the MATCH found a match in a row that's larger than the PPh column size.

@STUARTMAC 

If PPH is a number you could use SUMIFS().