Match Values on two sheets

[trevor2524]

So I'm trying to take the value found in Sheet 1 starting at A2. Switch to sheet 2 and Check Columns A & B to see if it finds a match. If it does then it will take the information from column c on sheet 2 and place it in column b on Sheet 1. If it doesn't find a match it will then just move onto the next one. The example I have given shows the beginning information on Sheet1 and Sheet2 and the final result is shown on Sheet3 and Sheet4. Any help is desperately needed. Thanks again.Test.xlsx

[alansidman]

Given your example, there are no exact matches.

I would suggest this as a solution, =IFERROR(OR(VLOOKUP(A2,Sheet2!$A$2:$C$4,3,FALSE),VLOOKUP(A2,Sheet2!$B$2:$C$4,2,FALSE)),"")
but it will fail because your item in sheet 2 cell B3 has the extra information Disc which Excel does not consider a match for the information in cell A3 in Sheet 1.

[basketball2524]

Yea that's the problem I see happening. On some of my files the number wont always be by itself it might have some wording before or after the phone number which is why I need to try and find a way for it to look through the entire cell for a match. The second sheet has the disc but the number at the beginning is the same as sheet 1 so it should bring back the correct information. I hope this helped explain exactly what I'm trying to do.

[alansidman]

I don't know of a solution but will pass it on to others in the forum to look at.

[FDibbins]

Trevor/basketball, please pick 1 ID and stick with it, so as not to confuse other members onthe forum

Thanks

[:) Sixthsense :)]

May be this…

In B2 Cell of Sheet1 - Array Formula - Requires CTRL+SHIFT+ENTER

=IF(MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$B$10)),ROW(Sheet2!$A$2:$B$10))),INDEX(Sheet2!C:C,MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$B$10)),ROW(Sheet2!$A$2:$B$10)))),"")

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Drag it down…

Extend the range Sheet2!$A$2:$B$10 to your desired range…

[trevor2524]

Sorry about that I was logged onto a different computer and didn't realize.

[trevor2524]

May be this…

In B2 Cell of Sheet1 - Array Formula - Requires CTRL+SHIFT+ENTER

=IF(MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$B$10)),ROW(Sheet2!$A$2:$B$10))),INDEX(Sheet2!C:C,MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$B$10)),ROW(Sheet2!$A$2:$B$10)))),"")

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Drag it down…

Extend the range Sheet2!$A$2:$B$10 to your desired range…

Thank You that worked.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

[trevor2524]

Glad it helps you and thanks for the feedback

Can I ask you one more thing kinda relating to this. How would you apply the fill effect in the macro so that it fills column b with the formula until column A has no more values.

[:) Sixthsense :)]

Try like this…

Please Login or Register  to view this content.