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?
 
Ejgil HedegaardCommented:
Try this in B2

=IF(COUNTIFS(Warehouse!$A:$A,$A2,Warehouse!$B:$B,"Joe",Warehouse!$C:$C,"<>"),"INSTALLED","SIGNED OUT")
0
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.