Vlookup

[hodges5378]

Here's my scenerio: I have two sheets. One is a query and one is manual. The issue I'm faced with is that I need some way of knowing when a new value is added to the query, so that I can go and add it to the manual sheet. Is there a way to create a column on the sheet with the query that looks up the value, and if it finds it on the manual sheet it will return a "0", and if it's not on the manual sheet it will return a "1". That way if I sum up that column and receive a sum of zero, then all of the values are correct, but if I get a 1 then I know I need to go back and add a value to the manual tab. Sorry if that was confusing. Thanks for the help!

[NBVC]

I think an Isna(Match()) formulation should do it....


e.g. =--Isna(Match(A1,Sheet2!A:A,0))

this will return a 1 if the item in A1 is not found in Sheet2 column A...and it will return a 0 if it is found.

Note: Make sure you right click the query table and select Data Range Properties and then check Fill formulas down in columns..... This will fill down the formula automatically when refreshing the query.

[hodges5378]

Thanks a lot!

[hodges5378]

I put in the formula and I'm getting 0's all the way down the board when there are a few values that are on the query tab but not the manual. I might have put in something wrong. Here's the formula I have.

=--ISNA(MATCH(A9,Manual-Test!C:C,0))

A9 is the first cell where the values begin, and Manual-Test is the name of the manual sheet. Let me know if you see anything wrong with the formula, or if you know why I am getting all 0's instead of a few 1's.

Thanks!

[NBVC]

I think it's because you need to enclose your sheet name in single-quotes. The hyphen in the sheet name is a special character and so Excel doesn't recognize the sheet name unless it is enclosed in single quotes...

try:

=--ISNA(MATCH(A9,'Manual-Test'!C:C,0)) copied down

[hodges5378]

Ok great...That did it...Here's another question. Instead of having it reference the sheet (Manual-test), is there a way to have it reference a range and look at the first column in the range? For instance, if my values are always going to come out of the first column in a range called "Manual_Input", is there a way to reference that first column of that range in the Match formula you gave me?