I have 2 lists on the same spreadsheet.

Column A has first names, Column B has surnames. These are listed in surname order currently.

Column G has first names, Column H has surnames, also currently listed in surname order.

Colums A & B are much longer than G & H.

I need to find which names in row G & H appear within the list in A & B rather than manually scroll between the 2 lists.

I have never used Lookup's or formulas of this nature and despite trying to find a way of doing this on the internet, I am having no luck - someone help me please - its driving me nuts!!!

Thank you

I need to find which names in row G & H appear within the list in A & B rather than manually scroll between the 2 lists.

Add this formulas to the mentioned cells:

C1:  =B1&", "&A1
D1:  =MATCH(C1,I:I,0)
I1:  =H1&", "&G1
J1:  =MATCH(I1,C:C,0)

and drag the formulas in C1:D1, resp. I1:J1 down to the end of each data.

When you want to combine both list, copy them below each other, then use Remove Duplicates in the Data tab.

Andreas.

Hello,

try this formula, starting in row 1 and copy down. 

=IFERROR("Found in row "&(MATCH(G1&H1,INDEX($A$1:$A$1000&$B$1:$B$1000,0),0)),"")

If you have more than 1000 names in column A, adjust the formula accordingly.

Hi vicky calver,

Based on your description, I test you issue in my own environment with IF and VLOOKUP formula.

The formula is: =IF(VLOOKUP(G2,$A:$B,2,FALSE)=H2,TRUE,FALSE)

As shown in the following figure. If the values in column G and column H match column A and column B at the same time, column I return True. If they didnt match, column I return False.

And I upload a sample via OneDrive, you can find this sample via this link:

http://1drv.ms/1HFwMf0

Hope its helpful.

Regards,

George Zhao
TechNet Community Support