Archive

Excel VLookUp Help

  • Scarlet_Buckeye
    I have an Excel spreadsheet.
    In Column A, I have data values in Rows 2 thru 35,526.
    In Column B, I have data values in Rows 2 thru 36,246.
    I'm interested in verifying if all of the data values in Column A, appear in Column B (and vice versa).
    I'm confident this can be done via the VLookUp function, but I need a little help.

    Example:
    Column A Column B
    Row 1 Data XYZ Data YYY
    Row 2 Data AAA Data PPP
    Row 3 Data ZZZ Data QQQ
    Row 4 Data XXX Data ZZZ
    Row 5 Data YYY Data KKK
    Row 6 Data BBB Data JJJ
    Row 7 Data CCC Data BBB
    Row 8 Data QQQ Data AAA
    Row 9 Data WWW Data XXX
    Essentially, I'd like to create a 3rd column (i.e., Column C) and 4th column (i.e., Column D); where... Column C uses a VLookUp of Column A to see if the data set/value in Column A appears in Column B. If It does, I would like for it to say "Yes" or simply copy the value and highlight it. If it doesn't, then I would like for it to say "No" or simply "N/A".

    And then something similar for Column D... where Column D uses a VLookUp of Column B to see if the data set/value in Column B appears in Column A. If It does, I would like for it to say "Yes" or simply copy the value and highlight it. If it doesn't, then I would like for it to say "No" or simply "N/A".

    Can anyone help me out with this? I know it's simple, but I'm having some trouble.

    Thanks in advance!
    Column A Column B Column C Column D
    Row 1 Data XYZ Data YYY "No" "Yes"
    Row 2 Data AAA Data PPP "Yes "No"
    Row 3 Data ZZZ Data QQQ "Yes" "Yes"
    Row 4 Data XXX Data ZZZ "Yes" "Yes"
    Row 5 Data YYY Data KKK "Yes" "No"
    Row 6 Data BBB Data JJJ "Yes" "No"
    Row 7 Data CCC Data BBB "No" "Yes"
    Row 8 Data QQQ Data AAA "Yes" "Yes"
    Row 9 Data WWW Data XXX "No" "Yes"
  • Commander of Awesome
    Control C+Control V column A to Column B. Or did I miss something. You want the same data to appear in both columns?

    Or I'd use an IF function. =IF(B2=C2, "Yes", "No")

    You can then filter to yes or no for the values you're looking for.
  • Scarlet_Buckeye
    Commander of Awesome;1530694 wrote:Control C+Control V column A to Column B. Or did I miss something. You want the same data to appear in both columns?

    Or I'd use an IF function. =IF(B2=C2, "Yes", "No")

    You can then filter to yes or no for the values you're looking for.
    Column A has 35,526 rows
    Column B has 36,246 rows

    That's a difference of 720 (i.e., 36,246-35,526). I'm trying to understand which cells in Column B are part of that "720" difference. However, I'm not guaranteed that every cell in Column A is necessarily in Column B. Thus, the real difference could be higher than "720."

    Make sense?

    I stumbled upon this little formula/equation --> =IF(COUNTIF($A$2:$A$35526,B2)=1,"Match","")

    So far, I think that might be doing the trick, but I'm not sold on it just yet.
  • gorocks99
    Insert a new column next to column A with "1" in every cell (down to row 35,526). Use the vlookup function in the column next to the old column B (now column c), =vlookup(C1,$A$1:$B$35526,2,0). If it comes back w/ a 1, it's in column A, if it comes back w/ a 0 it's not.
  • gorocks99
    Or, really, you don't need to insert a new column at all.

    In column C, just do =vlookup(B1,$A$1:$B$35526,2,0). If it returns anything, that value from column B can be found in column A. If it doesn't return anything, the value from column B is not in column A.