Forum Discussion
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..
what's the solution for this!?
here replicated file is attached..
See the attached version. The formulas are array formulas that have been confirmed with Ctrl+Shift+Enter, then filled down.
9 Replies
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.
- 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..
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.
- what should i do for that!!
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.
