VLOOKUP, MATCH or something else entirely?

  • Thread starter Thread starter Rebekah
  • Start date Start date
R

Rebekah

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!
 
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
 
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!
 
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
 
Back
Top