VLOOKUP, MATCH or something else entirely?
 

Please help!

I have two worksheets of data, both containing instructions against a
reference number. Which is the best way to find out if the reference number
is on both worksheets, and then record this?
I did this last week using VLOOKUP, but it was very messy, and I had to run
loads of pivot tables...

I'm using Excel 97

Many thanks!

VLOOKUP, MATCH or something else entirely?
 

Assume your reference numbers are in column A on both sheets. Use this
formula on sheet2:

=IF(ISNA(MATCH(A2:Sheet1!A:A,0)),"","on both sheets")

then copy down.

Hope this helps.

Pete

On Feb 2, 9:49*am, Rebekah wrote:
Please help!

I have two worksheets of data, both containing instructions against a
reference number. *Which is the best way to find out if the reference number
is on both worksheets, and then record this?
I did this last week using VLOOKUP, but it was very messy, and I had to run
loads of pivot tables...

I'm using Excel 97

Many thanks!

VLOOKUP, MATCH or something else entirely?
 

Hi! Thanks for getting back to me so quickly!

Could you talk me through the formula? Apologies if I sound stupid...

=IF(ISNA(MATCH(A2:Sheet1!A:A,0)),"","on both sheets")

I have 2 worksheets... The worksheet that I wish the response "on both
sheets" is "A", the worksheet that I need to match from is worksheet "B".
Which sheets/columns/cells should I be selecting at which point within the
formula?

Thank you for helping!

"Pete_UK" wrote:

Assume your reference numbers are in column A on both sheets. Use this
formula on sheet2:

=IF(ISNA(MATCH(A2:Sheet1!A:A,0)),"","on both sheets")

then copy down.

Hope this helps.

Pete

On Feb 2, 9:49 am, Rebekah wrote:
Please help!

I have two worksheets of data, both containing instructions against a
reference number. Which is the best way to find out if the reference number
is on both worksheets, and then record this?
I did this last week using VLOOKUP, but it was very messy, and I had to run
loads of pivot tables...

I'm using Excel 97

Many thanks!

VLOOKUP, MATCH or something else entirely?
 

Put the formula on row 2 of sheet A (eg in D2), and amend it to this:

=IF(ISNA(MATCH(A2,B!A:A,0)),"","on both sheets")

(sorry, I've just noticed that I had a : instead of a , in the earlier
formula), then copy the formula down.

The MATCH part of the formula is looking to see if A2 matches with
anything in column A of sheet B - if it doesn't match that part
returns an error (#N/A), but the ISNA part catches this error. If it
does match (i.e. there is a duplicate) then the match function will
return a number. So the formula basically says if there is an error
(no match) then return a blank cell, otherwise return the message "on
both sheets".

Hope this helps.

Pete

On Feb 2, 10:33*am, Rebekah wrote:
Hi! *Thanks for getting back to me so quickly!

Could you talk me through the formula? *Apologies if I sound stupid...

=IF(ISNA(MATCH(A2:Sheet1!A:A,0)),"","on both sheets")

I have 2 worksheets... The worksheet that I wish the response "on both
sheets" is "A", the worksheet that I need to match from is worksheet "B". *
Which sheets/columns/cells should I be selecting at which point within the
formula?

Thank you for helping!



"Pete_UK" wrote:
Assume your reference numbers are in column A on both sheets. Use this
formula on sheet2:

=IF(ISNA(MATCH(A2:Sheet1!A:A,0)),"","on both sheets")

then copy down.

Hope this helps.

Pete

On Feb 2, 9:49 am, Rebekah wrote:
Please help!

I have two worksheets of data, both containing instructions against a
reference number. *Which is the best way to find out if the reference number
is on both worksheets, and then record this?
I did this last week using VLOOKUP, but it was very messy, and I had to run
loads of pivot tables...

I'm using Excel 97

Many thanks!- Hide quoted text -

- Show quoted text -