Forum Discussion

DWFL1's avatar
DWFL1
Brass Contributor
Dec 19, 2017
Solved

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

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...
Formulas & Functions
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Dec 19, 2017

    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:

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

    https://exceljet.net/excel-functions/excel-vlookup-function

Clerk_495's avatar
Clerk_495
Copper Contributor
Oct 08, 2019

Are there any other alternatives to using Vlookup?

lukedeloosh's avatar
lukedeloosh
Copper Contributor
Oct 08, 2019

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/

  • Clerk_495's avatar
    Clerk_495
    Copper Contributor
    Oct 08, 2019

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 08, 2019

      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.

      • Clerk_495's avatar
        Clerk_495
        Copper Contributor
        Oct 08, 2019

        SergeiBaklan 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

Resources