SOLVED
Home

Match names between two sheets and return value of a cell in the row

%3CLINGO-SUB%20id%3D%22lingo-sub-138527%22%20slang%3D%22en-US%22%3EMatch%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-138527%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20looking%20for%20a%20way%20to%20match%20a%20name%20between%20two%20sheets%20and%20then%20return%20a%20date%20value%20which%20is%20in%20a%20different%20cell%20in%20the%20same%20row.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20in%20sheet%202%20if%20a%20site%20name%20in%20coulomb%20B%20matches%20a%20site%20name%20in%20sheet%201%20coulomb%20A%2C%20return%20the%20value%20from%20a%20specific%20cell%20in%20the%20same%20row%20as%20where%20the%20names%20matched.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20data%20is%20sorted%20on%20dates%20which%20may%20change%20and%20I%20need%20to%20be%20able%20to%20show%20the%20updated%20date%20value%20in%20sheet%202%20when%20date%20and%20order%20changes%20in%20sheet%201%20for%20a%20specific%20site%20name.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-138527%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-138615%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-138615%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20this%20worked%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-138560%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-138560%22%20slang%3D%22en-US%22%3E%3CP%3EA%20simple%20solution%20using%20VLOOKUP%20function%20is%20all%20you%20need.%3C%2FP%3E%0A%3CP%3EPlease%20see%20the%20attachment%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20you%20have%20to%20learn%20how%20to%20use%20the%20VLOOKUP%20correctly%20and%20safely!%3C%2FP%3E%0A%3CP%3EHere%20some%20sources%20about%20it%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FVLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EVLOOKUP%20function%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-vlookup-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EHow%20to%20use%20the%20VLOOKUP%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900004%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EAre%20there%20any%20other%20alternatives%20to%20using%20Vlookup%3F%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900032%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422263%22%20target%3D%22_blank%22%3E%40Clerk_495%3C%2FA%3Ecan%20use%20a%20combination%20of%20INDEX%20%26amp%3B%20MATCH.%20There's%20more%20variability%20with%20using%20this%20process%2C%20but%20it's%20trickier%20to%20wrap%20your%20head%20around.%20When%20I%20was%20doing%20a%20lot%20with%20Excel%2C%20VLOOKUP%20was%20always%20sufficient%20for%20my%20purposes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fhowtouseexcel.net%2Falternative-to-vlookup-index-match%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fhowtouseexcel.net%2Falternative-to-vlookup-index-match%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20helpful%20way%20to%20use%20VLOOKUP%2C%20if%20you%20don't%20have%20common%20cell%20data%20between%202%20sheets%20to%20use%20as%20the%20LOOKUP_VALUE%2C%20is%20to%20use%20a%20combination%20of%20CONCATENATE%20and%20VLOOKUP.%20With%20this%20method%2C%20you%20are%20creating%20unique%20values%20through%20concatenation%20first%20before%20vlookup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.exceltactics.com%2Ffaster-multiple-criteria-lookups-vlookup-concatenate%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.exceltactics.com%2Ffaster-multiple-criteria-lookups-vlookup-concatenate%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900187%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422274%22%20target%3D%22_blank%22%3E%40lukedeloosh%3C%2FA%3E%26nbsp%3BVlookup%20requires%20a%20table%20but%20whenever%20I%20create%20a%20table%20the%20excel%20spreadsheet%20removes%20it%20as%20well%20as%20all%20the%20formulas%2C%20does%20lookup%20Value%20do%20the%20same%3F%3C%2FP%3E%3CP%3EI%20will%20take%20a%20look%20at%20the%20INDEX%20%26amp%3B%20MATCH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900206%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422263%22%20target%3D%22_blank%22%3E%40Clerk_495%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20spreadsheet%20removes%20nothing%20if%20only%20you%20don't%20use%20*.csv%20format%2C%20and%20VLOOKUP%20doesn't%20require%20Excel%20Table%2C%20you%20may%20work%20with%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900221%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThe%20document%20I%20have%20been%20working%20with%20has%20a%20number%20of%20tabs.%20When%20I%20created%20tables%2C%20the%20document%20would%20not%20open.%20A%20note%20saying%20the%20file%20is%20corrupt%20comes%20up%20and%20Microsoft%20has%20fixed%20it.%20Then%20the%20list%20of%20tables%20I%20created%20come%20up%20to%20say%20the%20tables%20are%20gone%20and%20the%20formulas%20that%20were%20once%20all%20over%20the%20document%20are%20no%20longer%20there%20(just%20the%20numerical%20value%20of%20the%20formulas%20are%20left)%3C%2FP%3E%3CP%3EFor%20this%20reason%2C%20I%20gave%20up%20on%20vlookup%20and%20have%20replaced%20most%20of%20the%20vlook%20up%20with%20complex%20formulas.%20I%20have%20also%20worked%20on%20combining%20tabs%20to%20make%20it%20easier%20to%20work%20with.%20The%20INDEX%20MATCH%20might%20be%20what%20I%20need%20to%20achieve%20my%20updated%20workbook%3C%2FP%3E%3CP%3EStacey%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900249%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900249%22%20slang%3D%22en-US%22%3E%3CP%3EYeah%20INDEX%20MATCH%20doesn't%20work%20because%20the%20layout%20of%20the%20information%20added%20each%20month%20changes%20and%20the%20location%20of%20one%20cell%20may%20be%20in%20a%20different%20spot%20the%20next%20month%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900302%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20names%20between%20two%20sheets%20and%20return%20value%20of%20a%20cell%20in%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422263%22%20target%3D%22_blank%22%3E%40Clerk_495%3C%2FA%3E%26nbsp%3BI%20figured%20out%20a%20solution!%20I%20created%20a%20pivot%20table%20and%20just%20copied%20and%20pasted%20the%20data.%20I%20pull%20other%20data%20using%20a%20filter%20so%20a%20pivot%20table%20will%20also%20work%20for%20this%20situation.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dewalt Kotze
New Contributor

Hi

 

I am looking for a way to match a name between two sheets and then return a date value which is in a different cell in the same row.

 

So in sheet 2 if a site name in coulomb B matches a site name in sheet 1 coulomb A, return the value from a specific cell in the same row as where the names matched.

 

The data is sorted on dates which may change and I need to be able to show the updated date value in sheet 2 when date and order changes in sheet 1 for a specific site name. 

9 Replies
Solution

A simple solution using VLOOKUP function is all you need.

Please see the attachment file.

 

But you have to learn how to use the VLOOKUP correctly and safely!

Here some sources about it:

VLOOKUP function

How to use the VLOOKUP

Thanks this worked perfectly.

Are there any other alternatives to using Vlookup?

@Clerk_495can use a combination of INDEX & MATCH. There's more variability with using this process, but it's trickier to wrap your head around. When I was doing a lot with Excel, VLOOKUP was always sufficient for my purposes.

 

https://howtouseexcel.net/alternative-to-vlookup-index-match

 

Another helpful way to use VLOOKUP, if you don't have common cell data between 2 sheets to use as the LOOKUP_VALUE, is to use a combination of CONCATENATE and VLOOKUP. With this method, you are creating unique values through concatenation first before vlookup.

 

https://www.exceltactics.com/faster-multiple-criteria-lookups-vlookup-concatenate/

@lukedeloosh Vlookup requires a table but whenever I create a table the excel spreadsheet removes it as well as all the formulas, does lookup Value do the same?

I will take a look at the INDEX & MATCH

@Clerk_495 

Excel spreadsheet removes nothing if only you don't use *.csv format, and VLOOKUP doesn't require Excel Table, you may work with ranges.

@Sergei Baklan The document I have been working with has a number of tabs. When I created tables, the document would not open. A note saying the file is corrupt comes up and Microsoft has fixed it. Then the list of tables I created come up to say the tables are gone and the formulas that were once all over the document are no longer there (just the numerical value of the formulas are left)

For this reason, I gave up on vlookup and have replaced most of the vlook up with complex formulas. I have also worked on combining tabs to make it easier to work with. The INDEX MATCH might be what I need to achieve my updated workbook

Stacey

Yeah INDEX MATCH doesn't work because the layout of the information added each month changes and the location of one cell may be in a different spot the next month

 

@Clerk_495 I figured out a solution! I created a pivot table and just copied and pasted the data. I pull other data using a filter so a pivot table will also work for this situation.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies