Multiple Condition Formula checking for duplicates and values

Hey there!
So I am trying to come up with a formula that uses one cell as a reference, checks another sheets column for a match and if found check that same row but in other columns for values and if both conditions are met, enter specific text. For example:
Sheet Example 1Sheet example 2Use cell B2 from Sheet Joe's Truck for reference. Formula to go in Cell C2 (Status)
Condition 1:
If match is found in column A on Warehouse sheet AND Joe is entered in the same row column B on that sheet enter the text "SIGNED OUT"
Condition 2:
If match is found in column A on Warehouse sheet AND Joe is entered in the same row column B AND value is entered in Column C, enter the text "INSTALLED"

I have tried several IF, AND, COUNTIF and MATCH formulas but I just can't seem to get it right.

Thanks in advance for any help :)
Melodie MessinaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
Hi,

In your example here, what should the formula return Installed or Signed Out?
0
Melodie MessinaAuthor Commented:
Thank you for taking the time to respond! :)
I'd like it to be either, or, so if the first condition is met = SIGNED OUT, then if the 2nd condition is met = INSTALLED
0
abbas abdullaCommented:
Your question is not yet clear for me, The formula can return only one item in a cell. So with the screenshot you shown in the example above what the formula placed in joe's sheet cell C2 should return and why?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Melodie MessinaAuthor Commented:
Ok I'll try to explain best I can,
If the serial # on Joe's sheet is found on the Warehouse Sheet AND his name is in the same row, the status on Joe's sheet for that serial number should be SIGNED OUT
2.JPG1.JPGBUT if those two conditions are met AND the DATE INSTALLED column has a value in the same row of that serial #, the status should be INSTALLED
3.JPG4.JPG
0
Ejgil HedegaardCommented:
Try this in B2

=IF(COUNTIFS(Warehouse!$A:$A,$A2,Warehouse!$B:$B,"Joe",Warehouse!$C:$C,"<>"),"INSTALLED","SIGNED OUT")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Take a look at the attached using the DGET function.

Your original screenshot sample would not have met any criteria so would be blank. By changing the serial # to 789 it will now meet the criteria.

The DGET function relies on there being only one match for the criteria. If there are more than one entries that match then it will return an error.
DGET-Sample.xlsx
0
TracyVBA DeveloperCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Ejgil Hedegaard (https:#a42354242)
-- Rob Henson (https:#a42358071)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.