• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula with 4 match criteria

G

Glenda Johnson

New Member
Good morning,

I am working with a 2 page excel document. 1st sheet is tools moved "To Missing" and the 2nd sheet has tools moved "From Missing". I am having difficulties with the formula because I need to match both the serial number and the name on both sheets to see if a tool was returned by that person. Since everyone is issued the same items there are multiples issued, lost and returned on most items. If for example John Smith lost a screw driver, did he find it and return it later? I am attaching a sample document where I have changed the names and made up serial numbers. some will match, some will not. I have over 8000 lost items and over 3000 returned items in my original document and it will only grow more. I thought I had it but it is returning negative for everything which isn't right.

=IF($C$2:$C$3244&$E$2:$E$3244='To Missing'!$C$2:$C$8204&'To Missing'!$E$2:$E$8204,1,0)

Thank you in advance for your assistance.
 

Attachments

  • Example lost tools report.xlsx
    10.8 KB · Views: 0
i need to know if each individual person returned a tool that was put into a "lost" status under their name. I. E. they found it a week later in their pants pocket when they did laundry and returned it for credit (they get charged for lost tools). But every person has a screwdriver so there could me 10 returned screwdrivers and none of them could match John Smith. So I need the serial number and name off of the "to missing" sheet to match the serial number and name off of the "From Missing" sheet. (true/false) or (1, 0) or (yes/no), the person's name, all are exceptable. I hope this helps. Thank you so much Azumi.
 
Hi:

Please find the attached, the formulas in yellow colored column, I have just flagged it saying whether it is present in the 2nd tab or not, you can modify the flag as you wish.

Note the formula is a CSE formula execute it by pressing Control+Shift+Enter Key.

Thanks
 

Attachments

  • Example lost tools report.xlsx
    11.6 KB · Views: 0
WOW, thank you so much Nebu, that will work. You guys are the best. I have ordered books and read them, read online and tried different formulas for about a week now.

Thank you so much, i really appreciate.
Glenda.
 
You must log in or register to reply here.
Back
Top