Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What formula do I need in my worksheet DFW column C that will do the following?:

On worksheet DFW take data from cells A2 together with B2 and see if there is an exact match in worksheet Feb columns A and B - if there is, return a Y in DFW cell C2.

It does not necessarily need to be a “Y” in DFW column C – it could be 1 for Y and 0 for N, as long as it is not an error.

share|improve this question
add comment

2 Answers

This might be what you are looking for if I am reading the question right:

= IF (AND(DFW!A2 = Feb!A2, DFW!B2 = Feb!B2), "Y", "N")

It tests if cell A2 on the DFW worksheet matches cell A2 on the Feb worksheet and if the same is true for the two B2 cells return "Y" otherwise "N".

Updated answer based on comments:

=IF(ISERROR(MATCH(DFW!A2, Feb!A2:Feb!A5000, 0)), "N",  IF(DFW!B2 = LOOKUP(DFW!A2, Feb!A2:Feb!A50, Feb!B2:Feb!B5000), "Y", "N"))

This searches if DFW!A2 matches anything row in the A column on the Feb sheet. If not return "N". If there is a match on A it then checks B2 vs the corresponding row that was matched on A.

share|improve this answer
    
It is close to what I want except that I need it to search all of columns A & B in worksheet Feb like this: = IF (AND(DFW!A2 = Feb!A2:A5000, DFW!B2 = Feb!B2:B5000), "Y", "N") –  user206876 Mar 13 '13 at 20:27
    
Do you mean if the text in DFW!A2 matches any text in column A? –  Brad Patton Mar 13 '13 at 20:29
    
yes - so lets say it finds a match for DFW!A2 in Feb!A16 then I need to know if the text from DFW!B2 matches the text in Feb!B16 - if BOTH match in same row that is when i need the formula to return a Y in DFW!C2. –  user206876 Mar 13 '13 at 20:35
    
Updated the answer based on your latest comment –  Brad Patton Mar 13 '13 at 20:56
    
Brad your updated answer works except that I need to lock the cells in Columns A & B for worksheet Feb so that they dont roll up when the formula is copied down column C in worksheet DFW - when I do this using the $ to lock the cells the formula works if there is no match and returns a "N" but if there is a match it returns a "#N/A" instead of a "Y". Not sure why it does that?? –  user206876 Mar 14 '13 at 12:28
show 6 more comments

The catch with trying to deal with the columns individually is that MATCH with match_type 1 or -1 is not exact and with 0 only finds the first value that is exactly equal (I'm guessing you may have multiple instances). However the formula below may work for you (it is difficult to tell without sample data):

=IF(IFERROR(VLOOKUP(A2&","&B2,Range,1,FALSE),"N")="N","N","Y")

provided you are prepared to add a column to your Feb sheet with a formula in the top row of =A1&","&B1 copied down to the end (this column I have named Range).

There may well be a better way (eg an array formula) but I have not bothered to try to optimise this as I am not at all sure it meets your requirements.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.