Excel - Vlookup repeating values

Question

I've a huge excel table (more than 100000 lines) and I need to find the values that appear according to a dropdown list.

So I've implemented a VLOOKUP funcion:

=IFERROR(VLOOKUP($D$1;'table'!A2:$O$100000;2;FALSE); "-")

But the problem is that the function keeps repeating the same value, so for exemple, if there's only one appearence of a specific data, the funcion will repeat it has long the funcion exists.

I want to change it so if the value only appears one time, the funcion will only return one value, if it appears two times, the funcion only return two values, while the rest of the data only shows a - sign.

Example:

Example

When you say "return two values", do you want the two values in a single cell or in separate cells ?? — Gary's Student
Hi gary, separeted cells. Like in a row: 78888 78888 78888 78888 While there's only one 78888 value — Túlio Maia

Unknown Unknown · Accepted Answer · 2018-02-21T12:17:27

Try it as,

=iferror(index('table'!b:b, aggregate(15, 6, row($2:$99999)/('table'!a$2:a$99999=d$1), row(1:1))), "-")

Fill down for the second, third, etc. Make sure you at least as many rows of formulas as the maximum number of any single value.

Excel - Vlookup repeating values

1 Answers