- last edited on
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.
12-19-2017 08:37 AMSolution
10-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.
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.
10-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
10-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.
10-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
10-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
10-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.