Forum Discussion

Jayde1710's avatar
Jayde1710
Copper Contributor
Aug 13, 2022
Solved

Value repeats in vlookup

I'm creating a list from other sheet with vlookup formula but sometimes it returns same values as above cell.    =VLOOKUP($N$4,Sheet2!$A2:$M50,13,0)   I tried INDEX MATCH but the results are same...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Aug 15, 2022

    Jayde1710 

    See the attached version. The formulas are array formulas that have been confirmed with Ctrl+Shift+Enter, then filled down.

Jayde1710's avatar
Jayde1710
Copper Contributor
Aug 13, 2022
i have to make a list from monthly data file..but there are 4 different values like "15021" and it is repetitive.. everytime the value changes vlookup returns same values as above.. i can use filters but there is other data too.. copy-pasting will take time and sometimes create problems if you missed something..
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 13, 2022

Jayde1710 Can you share a file (OneDrive or similar) that more accurately reflects the data you are dealing with and include a sheet with the output you would like to get? Excel has great built-in tools that can rearrange, filter and tidy-up data. Which one will work best for you depends on the data and the Excel version you work with.

  • Jayde1710's avatar
    Jayde1710
    Copper Contributor
    Aug 15, 2022
    I can't share the exact data file..but i attached a replicate file.. and I'm still working on 2007 so new Excel formulas not working
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Aug 15, 2022

      Jayde1710 Okay! that's an old Excel version. don't really remember what works there and what not.

      Thanks for the file. I created a pivot table from the SALE table and used the "Show Report filter Pages..." button to display the listings for the individual grades, each in their own pivot table. Is that something you had in mind?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 15, 2022

      Jayde1710 

      See the attached version. The formulas are array formulas that have been confirmed with Ctrl+Shift+Enter, then filled down.

      • Jayde1710's avatar
        Jayde1710
        Copper Contributor
        Aug 15, 2022
        wow..that formula works..thank you buddy..

Resources