VLOOKUP help

[austenr]

I have 2 worksheets with one column of data on each sheet. Sheet2 is my master list and Sheet1 is my weekly sheet where I want to display "No Macth" if the entry does not match the list on Sheet2. I have attached my zip file where I attempted to accomplish this process. The list should be around 500 entries each. Thanks in advance to anyone that can help.

[Zack Barresse]

Hi Austen,

You already have formulas in there. The one on sheet 2 is wrong, but what do you want that differs from what you have already?

Btw, change the formula on sheet 2, in A1 enter and copy down ...

=IF(COUNTIF(A1,Sheet1!A:A),"","No match")

You had referenced Sheet2!, which only counts the same sheet, hence always a match.

[austenr]

I am a little confused. A1 has names in it or is that where I should put the formula and the names. Could you alter my zip file. I think I need a visual on this. Thanks a lot Zack. I have always struggled with this kind of lookup.

[Zack Barresse]

Well let me see if I understand this right. You are just wanting a visual check in column B of both sheets. The test is to see if the associated value in column A of each respective sheet is in column A of the other respective sheet. So sheet1 will check colA of sheet2, and vice versa. Is that right?

And btw, many apologies Austen, I should have said B1 on sheet 2 enter the formula. A1 is the names, right. Sorry.

[Johannes]

I think you could also use the MATCH function:

=IF(ISERROR(MATCH(A1;SHEET2!A:A;0));"No Match";"")

[austenr]

Also what can you do to check other data in workbooks that are not open. Right now there is a backlog of a couple of months and these files are sent to us weekly. Also, would this be something that I could accomplish in Access? This is for someone in my office and I would like to make the process as simple as possible for them because they are really computer challenged. Thanks to everyone who responds.

[Zack Barresse]

For files that are not open you can download/install the morefunc add-in and make use of the INDIRECT.EXT function. This works half-decent imho, I think it leaves a little to be desired, but does work. Otherwise you can just link to a cell in another workbook and when you go to Tools --> Options --> Calculations (tab), make sure your Update Remote References and Save External Link Values are checked.

As far as putting something like this in Access, I think it really depends on how much information you are looking to put in here and what you are looking to do with it. As Excel is not a 'true' database, it does not do things inherently that Access will (I do not know Access that well, and don't claim to). Me personally, I do everything in Excel.