Dec 19 2017
07:04 AM
- last edited on
Jul 25 2018
10:36 AM
by
TechCommunityAP
Dec 19 2017
07:04 AM
- last edited on
Jul 25 2018
10:36 AM
by
TechCommunityAP
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.
Dec 19 2017 08:37 AM
SolutionA 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:
Dec 19 2017 10:53 AM
Thanks this worked perfectly.
Oct 08 2019 09:47 AM
Are there any other alternatives to using Vlookup?
Oct 08 2019 10:02 AM
@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/
Oct 08 2019 11:08 AM
@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
Oct 08 2019 11:17 AM
Excel spreadsheet removes nothing if only you don't use *.csv format, and VLOOKUP doesn't require Excel Table, you may work with ranges.
Oct 08 2019 11:23 AM
@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
Oct 08 2019 11:35 AM
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
Oct 08 2019 11:59 AM
@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.
Dec 19 2017 08:37 AM
SolutionA 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: