0

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
11
  • 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
    Commented Mar 13, 2013 at 20:27
  • Do you mean if the text in DFW!A2 matches any text in column A?
    – Brad Patton
    Commented Mar 13, 2013 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
    Commented Mar 13, 2013 at 20:35
  • Updated the answer based on your latest comment
    – Brad Patton
    Commented Mar 13, 2013 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
    Commented Mar 14, 2013 at 12:28
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .