Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet 3

Make a copy of your workbook and test the following on the copy.

Please Login or Register  to view this content.

Originally Posted by Guji

Just had a thought is it possible to highlight the cell in sheet 1 if it doesnt match the cell in sheet 2???

Please Login or Register  to view this content.

If you don't want to use a macro, here is another solution

Put this formula in Sheet 3 cell A1 and copy right to Column Q and down as many rows as you need to:

=OFFSET(Sheet2!$A$1,MATCH(Sheet1!$F7,Sheet2!$B$2:$B$6234,0),COLUMN()-1)

The MATCH(Sheet1!$F7,Sheet2!$B$2:$B$6234,0) part of the equation finds the row number if the email addresses in Sheets 1 and Sheet 2 match

The OFFSET(Sheet2!$A$1,MATCH(Sheet1!$F7,Sheet2!$B$2:$B$6234,0),COLUMN()-1) returns the value in column A that matches the criteria
By copying the equation to the right to column Q, you copy the rest of the columns on that row

NOTE:
This approach does not pick up if an email is entered more than once (For example, [email protected]



Kirk

@ AlvaroSiza

Thanks for this works 100%. Your assistance is really appreciated

Hi AlvaroSiza

I wonder if you could assist with my query! The assistance you provide previously with above has been proven invaluable, I however do face a little bit of a problem. I sometimes get a false response and was wondering if there is anyway of overcoming this.

eg:
users with email address [email protected] and [email protected]. I need to match [email protected] but I have found that because the second address appears as the first entry in my lookup sheet, I am returned that entry which is incorrect.

Is there a way to get around this?

Within this line of code

Please Login or Register  to view this content.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

See if that fixes it and report back.