Need Assistance with Complex Formula

[meppwc]

I am having difficulties with creation of a formula to perform what I have described below:

The formula for the logic below will exist in cell C2 of the IMAC-D Summary worksheet:
If the value in cell J2 of the IMAC-D Summary worksheet DOES NOT EXIST in column A of the Lookup – Aged worksheet, then return “SN NOT FOUND”
If the value in cell J2 of the IMAC-D Summary worksheet DOES EXIST in column A of the Lookup – Aged worksheet, AND, if the value in F2 of the IMAC-D Summary is “Disposal”, then,
If the value in column B of the Lookup – Aged worksheet is greater than 7.99, then return “In Scope”
If the value of column B of the Lookup – Aged worksheet is less than 8.00, then return “OOS

 

[Eric W]

Try:

=IFERROR(IF(VLOOKUP(J2,'Lookup - Aged'!A:B,2,0)<8,"OOS","In Scope"),"SN NOT FOUND")

 

[Eric W]

Forgot the Disposal part, try:

=LET(amt,VLOOKUP(J2,'Lookup - Aged'!A:B,2,0),IF(ISERROR(amt),"SN NOT FOUND",IF(F2="Disposal",IF(amt<8,"OOS","In Scope"),"Found, but f2 <> Disposal")))

 

[meppwc]

Formula returns "

SN NOT FOUND in every cell

 

[meppwc]

I figured it out.........my mistake..........thanks so much Eric........this is a great assistance to me.

 

[Eric W]

Happy to help! Thanks for the feedback. ?