Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
second or third match in vlookup() or Match() | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions |