Verify data from sheet to sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1756639%22%20slang%3D%22en-US%22%3EVerify%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1756639%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20have%20data%20change%20on%20sheet%201%20based%20on%20data%20in%20sheet%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20would%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20sheet%201%20column%20B%20a%20list%20of%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20sheet%202%20column%20A%20a%20list%20of%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20in%20sheet%201%20column%20R%20row%204%20to%20execute%20some%20sort%20of%20lookup%20to%20scan%20sheet%202%20column%20A%20looking%20for%20a%20name%2C%20and%20if%20it%20finds%20a%20name%20that%20matches%20sheet%201%20column%20A%20row%204%20then%20it%20puts%20a%20T%20in%20sheet%201%20column%20R%20row%204.%20If%20no%20match%20is%20found%20it%20puts%20a%20U.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20if%20this%20sounds%20confusing%2C%20not%20even%20sure%20its%20possible.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1756639%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1757502%22%20slang%3D%22en-US%22%3ERe%3A%20Verify%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1757502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824194%22%20target%3D%22_blank%22%3E%40rrbailey%3C%2FA%3E%26nbsp%3BThat%20could%20be%20in%20R4%20on%20Sheet1%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNA(VLOOKUP(A4%2CSheet2!A%3AA%2C1%2CFALSE))%2C%22U%22%2C%22T%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1757506%22%20slang%3D%22en-US%22%3ERe%3A%20Verify%20data%20from%20sheet%20to%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1757506%22%20slang%3D%22en-US%22%3EThanks!%20I'll%20give%20it%20a%20try.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20it%20reversible%20as%20well%20to%20check%20sheet%201%20against%20sheet%202%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello! 

 

I am trying to have data change on sheet 1 based on data in sheet 2.

 

This is what I would like:

 

I have sheet 1 column B a list of names.

 

I have sheet 2 column A a list of names.

 

I would like in sheet 1 column R row 4 to execute some sort of lookup to scan sheet 2 column A looking for a name, and if it finds a name that matches sheet 1 column A row 4 then it puts a T in sheet 1 column R row 4. If no match is found it puts a U.

 

Sorry if this sounds confusing, not even sure its possible.

6 Replies

@rrbailey That could be in R4 on Sheet1:

=IF(ISNA(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),"U","T")

 

Thanks! I'll give it a try.

Is it reversible as well to check sheet 1 against sheet 2?

@rrbailey Sure, no problem. The first element inside the VLOOKUP (A4) is what you want to search for and the second part is where you want to search for that value (Sheet2!A:A). The 1 tells Excel to return the value in the first column (in this case you only search in one column, i.e. column A in Sheet2) and FALSE (or 0) means that it has to be an exact match.

 

Normally, this will return the name found or #NA! if not found. But since you want "T" if found and "U" if not, I had to wrap the VLOOKUP in IF and ISNA() statements.

 

More about VLOOKUP (or its successor XLOOKUP) here:

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 

@Riny_van_Eekelen will the names in the various sheets not being in the exact same order matter? For example if "Bob" is in row 4 on sheet 1 but row 3 on sheet 2 will T still be properly applied to R4 in sheet 1?

@rrbailey The VLOOKUP function (or XLOOKUP) will look-up a value in another range of cells. Is doesn't matter how that range of names is sorted or where the names are witting that range. If it exists anywhere in that range, it will return the matching value.

Now, in some instances your lookup range must be sorted, but I don't believe that is relevant in your case. 

Great! The goal is the find "Bob" in sheet 2 in X row and Match it to Bob in sheet 1 in some other random row and if it's there place the value.

I'm looking forward to trying this out later on, thanks for your help!