VLOOKUP with multiple rows

[Pumperkin]

Hi all,

Trying to write a formula but getting stuck.

I have a list of around 30 items that are "Not Despatched". This list changes and I want to pull the updated list (which may be in different row numbers as time goes by, and there may be greater or fewer lines).

I tried VLOOKUP however given that I am only V lookingup "Not Despatched", it only brings up the data that appears first. I can't make the formula unique, as I don't know what I'm looking up (other than it being Not Despatched) before it's looked up.

Is there a way to do this other than just copying and pasting the list across?

TIA!

 

[JimM]

Do you really need a formula or would a simple filter work?

 

[Pumperkin]

A formula would be better as I don't want to have to constantly refresh the filter (I am pulling the data to another workbook).

 

[Fluff]

If you want a separate list, you could use something like

Book1
	A	B	C	D	E	F
1						Not Despatched
2	Car		Not Despatched			Car
3	Bike					TV
4	Sofa					Mobile Phone
5	TV		Not Despatched
6	Computer
7	Mobile Phone		Not Despatched
Sheet3

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/($C$2:$C$7=$F$1), ROWS($F$2:$F2))),"")

 

[Pumperkin]

If you want a separate list, you could use something like

	A	B	C	D	E	F
1						Not Despatched
2	Car		Not Despatched			Car
3	Bike					TV
4	Sofa					Mobile Phone
5	TV		Not Despatched
6	Computer
7	Mobile Phone		Not Despatched

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula F2 =IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/($C$2:$C$7=$F$1), ROWS($F$2:$F2))),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks for the help - I have managed to sort it out slightly differently via ARRAY

=IFERROR(INDEX('[Days to clear stock.xlsm]Shipped'!$Q$2:$Q$1172, SMALL(IF(F$1='[Days to clear stock.xlsm]Shipped'!$M$2:$M$1172, ROW('[Days to clear stock.xlsm]Shipped'!$Q$2:$Q$1172)-1,""), ROW()-2)),"")

 

[Fluff]

Glad you sorted it & thanks for the feedback