+ Reply to Thread
Results 1 to 31 of 31

need a formula that searches and matches 4 columns across to sheets and displays a value

  1. #1
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    need a formula that searches and matches 4 columns across to sheets and displays a value

    hi guys

    I am looking for a macro or formula for this please: Basically, as you can see above I have 2 sheets, I need a marco that will place the result in E1,2,3 etc on Sheet 2. The marco/formula needs to look at Sheet1 Columns C and D, find exact match in the same columns on sheet 2, then give a value in sheet 2 cell E.


    Sheet 1

    C1 - Scots
    C2 - Scots

    D1 - EH13
    D2 -EH12


    Sheet 2

    C1 - Scots
    C2 - Scots

    D1 - EH13 (gives a 0 in E1 sheet 2 as its found a match)
    D2 - EH1 (gives a 1 in E2 sheet 2 as its has not found a match)



    as ever thank you guys in advance for any help you can help

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    With VBA Macro

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    hi dave, thank you

    i need it till match both C1 and D1 against C1 and D1 on the other sheet and not C1 - match C1 etc

    what i have done is added a =(C2&D2) forumal in Cell E (Both sheets) which then gives me ScotsEH13, then i used =IF(ISNA(VLOOKUP(E,Sheet1!$E:$E,1,FALSE)),"NO","YES") to give a yes or no on if it matches. But it does not match as it reads the code and not the writing SCOTSEH13

    does that make sense

  5. #5
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    So you want to check if it corresponds to the exact same cells on sheet1 as opposed to anywhere in those columns?

    if so:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Otherwise I do feel the solutions provided do what you need.

    otherwise please upload a sample workbook.

  6. #6
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    here the sheet

    In sheet2 Cell D - i need a 0 if columns B+C match on each sheet, if not a 1
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Not letting me open that file.

    What does this not do correctly for you? (Paste in D1 and drag down)

    For EXACT MATCH only in same relative cell e.g. C1=C1 and D1=D1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For Dynamic Match search all cells in column e.g C1 = ANY value in column C and D1 = Value in Column D for same row as match in column C

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    N.B - We should fix the range of C and D if searching entire column with content.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    In Sheet2

    D2

    =IFERROR(IF(MATCH(B2&C2,Sheet1!$B$2:$B$111&Sheet1!$C$2:$C$111,0),0,1),1)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  9. #9
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Dave

    mate, i am sorry to be a pain, the dynamic match works on the test sheet but when I change to work on the actual sheet it does not.

    so I need it to match sheet 1 Column M (start cell2) and N (start cell2) against sheet 2 Column A (start cell2) and B(start cell2)

    if this cant be done ill just delete the other cells

  10. #10
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This sounds like it should do it.

  11. #11
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    dave


    sorry, it was because of the size.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113
    Hi john,

    Sorry I've just noticed this, I've had issues logging in, I will try this formula

    Thank you for your time

    Quote Originally Posted by JohnTopley View Post
    In Sheet2

    D2

    =IFERROR(IF(MATCH(B2&C2,Sheet1!$B$2:$B$111&Sheet1!$C$2:$C$111,0),0,1),1)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  13. #13
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Still need help guy, I greatly appreciate people taking the time to help.

    So everyone aware I have upload a test spreadsheet, I need a formula that will look at the 2 cells on sheet 1 and look for a match somewhere on sheet 2, if there is a mate it puts a 1 in the third column on sheet 2

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    See attached: formula as previous post with required ranges.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    John has already uploaded his working version, however for what it's worth I've added mine in also.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    PFDAVE

    i have just downloaded your sheet, i applied the formula to sheet 1 column O as there could be duplicates rows in there and therefore it would find and score better, however i have noticed a few 0 for the PARA within the first 20 rows, there are no matchs for these on Sheet 2

    can you take a look please

  17. #17
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    John,

    i have done the same to yours and apllied it to column O on sheet 1, however im only getting a 0 return for all areas mate, so stating theres no matchs at all. can you help

  18. #18
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    i know this my fault guys as i dont think i have explained myself that well and i greatly appricate you guys helping. on sheet one, both columns make up the information (example Para PE25) i need something that will take those columns and look for a duplicate on sheet 2. So if it finds PARA PE25 (together) on sheet 2 it gives a 0 and if it does not find it a 1. the formula needs to look for both words together.

    i do have a macro on another spreadsheet that looks for duplicates like this and then turns both cells yellow, would this help? so instead of turning the cells yellow it gives a 1 or 0 in another box?

  19. #19
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Quote Originally Posted by mickgibbons1 View Post
    PFDAVE

    i have just downloaded your sheet, i applied the formula to sheet 1 column O as there could be duplicates rows in there and therefore it would find and score better, however i have noticed a few 0 for the PARA within the first 20 rows, there are no matchs for these on Sheet 2

    can you take a look please
    OK maybe the 0 and 1 is actually the wrong way round

    change ,1,0) at the end to ,0,1)

    This will display 0 if there IS a match and 1 if there is NOT a match

  20. #20
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Dave

    please could you do me a favour and upload your test sheet again but with the formula writen to give a 1 or 0 in column O and sheet 1 please

    so it compares columns L+M (together) and finds a match on sheet 2 but counts it on sheet 1

    sorry to be a pain

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    So everyone aware I have upload a test spreadsheet, I need a formula that will look at the 2 cells on sheet 1 and look for a match somewhere on sheet 2, if there is a mate it puts a 1 in the third column on sheet 2
    This is the opposite to your request in post #18. (assuming "mate" = "match")

    The formula i provides checks if A & B match L & N (not M) and returns 1 if MATCHED, 0 if not.

  22. #22
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    John Typo mate sorry

    you are correct, How do i change your formula to give a 0 if match and 1 if not and for it to display in Sheet 1 column O and not sheet 2

  23. #23
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Hi Mick, I've updated and attached.
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    dave can you double check as its the same as before

  25. #25
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    It's not the same,

    First workbook

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Second workbook

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Hi dave

    thank you once again, when i apply this formula to Column O on sheet one im still getting matchs that i know are not there, can you suggest anything?

  27. #27
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Quote Originally Posted by mickgibbons1 View Post
    Hi dave

    thank you once again, when i apply this formula to Column O on sheet one im still getting matchs that i know are not there, can you suggest anything?
    That's because your values in column B sheet2 have a trailing space behind them all which is why I had to use trim in the original solution.

    Trim those values and you will get the results you want in sheet1.

    You can trim using either this formula =TRIM(B1) then drag down and copy and paste as values over the contents of column B, or place this code into VBA and run the macro after highlighting the contents of column B

    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Dave

    thank you, i understand that one for a change and that macro will come in very handy


    i have applied the formula to Column O, it is still showing the PARA as a 1 and then 0 further down, the PARA should show all the same valve as they are not on Sheet 2. can you take a look please
    Attached Files Attached Files

  29. #29
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Is this what you're looking to achieve?

    Paste this is O1 and drag down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Currently your putting the result in Row1 of Sheet1 but searching the values in Row 1 Sheet2.

  30. #30
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    113

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    Dave

    this works a treat mate, that you so much, learning alot here

  31. #31
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: need a formula that searches and matches 4 columns across to sheets and displays a val

    No problem Mick, glad we got there in the end. Thanks for the rep and give me a shout if you get stuck

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need a marco that searches and matches 4 columns across to sheets and displays a value
    By mickgibbons1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2017, 05:14 AM
  2. Using a VLOOKUP VBA that searches and matches by headers not columns
    By CrypTrick in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2015, 05:10 PM
  3. Replies: 4
    Last Post: 07-24-2015, 02:26 AM
  4. Replies: 7
    Last Post: 04-11-2013, 12:46 AM
  5. [SOLVED] Compare Columns A& B on Sheets M and D, if A matches and B doesnt Copy B, else new row
    By Spyderz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-12-2012, 05:55 PM
  6. If no matches formula displays #N/A
    By Weasel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-02-2008, 06:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1