how to match data from two almost identical lists

**ITV** · 08-20-2009, 06:21 PM

Hi everyone,

I'm no expert with excel and I'm working on two almost identical lists but because of their nature, some rows are repeated on one of the sheets and so on. I need help matching some cells onto the other sheet. I tried the VLOOKUP formula but it doesn't work since some rows are repeated... I need the match to link especifically a Name with it's correspondent Value, regardless of the position of the name in either of the sheets and whether it is repeated or not.

Any help will be much appreciated!!!!!

**NBVC** · 08-20-2009, 09:04 PM

To match one cell to one vertical column of cells, use:

=Isnumber(Match(A1,Sheet2!A:A,0)) which will return TRUE if match found...

if you want to match to cells with 2 vertical columns...

=ISNUMBER(MATCH(1,INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10=B2),0),0)) where you must use defined ranges instead of A:A...

If you are looking to return data from another column.. where exact match is found in 1st or 1st and 2nd columns.. then use Index() around either of the above..

e.g.

=Index(C:C,atch(A1,Sheet2!A:A,0))

or

=Index($C$1:$C$10,MATCH(1,INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10=B2),0),0))

**ITV** · 08-25-2009, 04:15 PM

Thank you very much!

However, I think I'm a bit lost so I will try to give a better example of what I'm aiming at. Basically what I would like to do is copy the coordinates available on sheet 2 to their corresponding Engagement on sheet 1. When there's an Engagement on Sheet 1 that doesn't match up with any on Sheet 2, would like it to return either blank or "missing" or something like that.

I've attached a workbook as an example.

Please note that there are several differences between sheet 1 and 2:
- The Engagement list on both sheets is similar, but not identical:
- The Engagements don't follow the same order on Sheet 1 and 2
- Sheet 2 may have some engagements repeated on several rows, whereas on Sheet 1 you will find them only once.
- There are some Engagements on Sheet 1 that won't appear on Sheet 2.
- Sheets 1 and 2 don't have the same column position for latitude and longitude.

I appreciate any help you can give to resolve this!

**NBVC** · 08-25-2009, 04:22 PM

Try:

=IF(ISNUMBER(MATCH($A2,Sheet2!$A$1:$A$6,0)),VLOOKUP($A2,Sheet2!$A$1:$E$6,4,0),"")

in B2

and

=IF(ISNUMBER(MATCH($A2,Sheet2!$A$1:$A$6,0)),VLOOKUP($A2,Sheet2!$A$1:$E$6,5,0),"")

in C2

both copied down.

**ChemistB** · 08-25-2009, 04:35 PM

In B2 Copied down

Please Login or Register  to view this content.

In C2 copied down

Please Login or Register  to view this content.

Modify your ranges to suit your spreadsheet. Does that work for you?

**ITV** · 08-25-2009, 11:34 PM

Oh wow! Worked great on that example workbook! Will test it out tomorrow at the office on the real workbook. I'll be posting the results then.

Thanks so much, owe you a beer!

**ITV** · 08-26-2009, 11:01 AM

The formula worked perfectly on the sheet. Thanks a lot!

**NBVC** · 08-26-2009, 01:06 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save