Value repeats in vlookup

[Jayde1710]

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.. 

what's the solution for this!?

here replicated file is attached..

 

 

[#2]

@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.

[#3]

what should i do for that!!

[#4]

@Jayde1710 Not sure I understand what you want to achieve. Can't you just use a filter or, if you are on a modern Excel version, the FILTER function.

[#5]

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..

[#6]

@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.

[#7]

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

[#8 · Accepted solution]

@Jayde1710 

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

[#9]

wow..that formula works..thank you buddy..

[#10]

@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?