# Formula help for matching data from multiple columns in multiple worksheets

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.

-

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".

``````=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.

-
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

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.

-