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.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 13, 2022

Jayde1710 That's because VLOOKUP returns the first match. You lookup array shifts down one row for every cell in N. When the firs row of the lookup range is row 9 in Sheet2 (i.e. in cell N12 of Sheet1), the first match for 15021 is found on row 10 (941). Then, in N13, the first match is again on row 10. Thus, a repetition of the previous row.

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?

Resources