function similar to VLookup but returns every value relevant

Question

I am having some trouble finding a function that will allow me to take a list of data and return all the values relevant to a reference cell. The idea of it works like a Vlookup except Vlookup only returns the first value it sees.

I have thought about using a pivot table and/or using filters on my list but I run into problems with those because of the complexity of the workbook.

This is essentially what my workbook would look like:

    A         B       C
 1 Pen      Blue     200
 2 Marker   Red      150
 3 Pen      Black    180
 4 Pen      Green    170
 5 Marker   Green    125

I am trying to find a funtion to return this:

    A       B       C
 1 Pen    Blue     200
 2 Pen    Black    180
 3 Pen    Green    170

All I need it to do is to recognize Column A's value as "Pen" and bring back the color with it in Column B. Column C I can then use a Vlookup to get.

I feel like I am missing something to help someone understand and be able to help me but I can't pinpoint what it is... if you need something else as clarification let me know

If you filter on Pen, what happens? Is it not exactly what you showed? If your workbook is complex (which doesn't seem to be the case) we would need to know how :) — David G
You can use array formulas but you will have to cap the number of lines — exussum
@DavidGM What is returned is then used in a vlookup on a different sheet which is then used in a pivot table. The problem with a filter is that all it does is hide the other data. I can't use the vlookup in a different sheet without it recognizing all the data that the filter is simply hiding. Now you might be thinking, well the Vlookup will only return the value if it matches the criteria. The problem is that I don't have a criteria for it to match until the data is filtered through. The data I provided is a much simpler version of what I have. For example, rather than Blue it is a unique.. — THAT newbie
code such as XYZ_WEB_AUTH_B0001.... I hope this helps a bit more — THAT newbie
@exussum that was my other thought but the list is constantly being maintained so I can't cap the number of lines — THAT newbie

Latch Latch · Accepted Answer · 2015-08-04T15:47:25

This can be done with an array formula.

First, fill the first column with numbers 1 to the number of "Pen" in your lookup range.

Then, enter the formula:

=INDEX(A$1:A$5,SMALL(IF($A$1:$A$5="Pen",ROW($A$1:$A$5),""),$A11))

Where $A11 is the cell where you started your enumeration from the first step.

Then press ctrl+shift+enter to make it an array formula.

The "if" returns an array of row numbers where the cell value is "Pen" (which can obviously be changed to other values if needed). Then the small returns the nth smallest, which is why we need the first column to be numbers 1 to numbers of "Pen" in range. Then the index returns the result cell in the specified range for that row. Be careful, this formula assumes your table starts at A1 (as per your example), but can easily be ajusted if that's not the case.

You can then drag it to the right and down and the formula will adapt as long as you keep the $ in the same places they are now.

The output will look like (assuming you start at A11 like I did):

     A       B       C        D
 11  1      Pen    Blue     200
 12  2      Pen    Black    180
 13  3      Pen    Green    170

function similar to VLookup but returns every value relevant

3 Answers