REF! error in vlookup

[PeakyBlinder]

Afternoon

I have a vlookup formula in which I am getting a REF! error. I have probably over complicated the formula but are struggling to rectify the error. The lookup data field could be either a number of a text field so I have tried to build the it and to complicate things if the lookup is false I want it to try a 2nd lookup using only the text or number before the character -

There is also a calculation using the vlookup

This is the formula:

=SUM(IF(ISERROR(+VLOOKUP(TEXT($A4,"@"),vMC_PARKERS_OP_TIMES!$A:$E,5,0)),+IF(ISERROR(VLOOKUP(LEFT($A4,(FIND("-",$A4,1)-1)),vMC_PARKERS_OP_TIMES[[PartNumber]:[ResourceCode]],5,0)),VLOOKUP($A4,vMC_PARKERS_OP_TIMES[[PartNumber]:[ResourceCode]],5,0),VLOOKUP(LEFT($A4,(FIND("-",$A4,1)-1)),vMC_PARKERS_OP_TIMES[[PartNumber]:[ResourceCode]],5,0)),+VLOOKUP(TEXT($A4,"@"),vMC_PARKERS_OP_TIMES!$A:$E,5,0)))*Qty!C4+SUM(IF(ISERROR(+VLOOKUP(TEXT($A4,"@"),vMC_PARKERS_OP_TIMES!$A:$E,4,0)),+IF(ISERROR(VLOOKUP(LEFT($A4,(FIND("-",$A4,1)-1)),vMC_PARKERS_OP_TIMES[[PartNumber]:[ResourceCode]],4,0)),VLOOKUP($A4,vMC_PARKERS_OP_TIMES[[PartNumber]:[ResourceCode]],4,0),VLOOKUP(LEFT($A4,(FIND("-",$A4,1)-1)),vMC_PARKERS_OP_TIMES[[PartNumber]:[ResourceCode]],4,0)),+VLOOKUP(TEXT($A4,"@"),vMC_PARKERS_OP_TIMES!$A:$E,4,0)))

any assistance would be most appreciated

 

[jasonb75]

That's a long formula to try and make sense of without the data source to evaluate it.

As you are getting a #REF! error, it means that a range used in the formula does not exist. Excel would not accept a formula that has an incorrect table name so the only likely thing would be an incorrect worksheet name. Should vMC_PARKERS_OP_TIMES!$A:$E have underscores or spaces? If spaces then you will need to correct that and enclose the sheet name in single inverted commas 'vMC PARKERS OP TIMES'!$A:$E

If that sheet is used multiple times in the formula then you would need to correct all of them.