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

Try:

Formula:

Please Login or Register  to view this content.

Regards

With VBA Macro

Please Login or Register  to view this content.

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

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:

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.

here the sheet

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

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:

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:

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.

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.

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

Formula:

Please Login or Register  to view this content.

This sounds like it should do it.

dave


sorry, it was because of the size.

Hi john,

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

Thank you for your time

Originally Posted by JohnTopley

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.

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

See attached: formula as previous post with required ranges.

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

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

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

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?

Originally Posted by mickgibbons1

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

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

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.

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

Hi Mick, I've updated and attached.

dave can you double check as its the same as before

It's not the same,

First workbook

Formula:

Please Login or Register  to view this content.

Second workbook

Formula:

Please Login or Register  to view this content.

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?

Originally Posted by mickgibbons1

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.

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

Is this what you're looking to achieve?

Paste this is O1 and drag down:

Formula:

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.

Dave

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

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