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
Not Despatched
Car	Not Despatched	Car
Bike	TV
Sofa	Mobile Phone
TV	Not Despatched
Computer
Mobile Phone	Not Despatched

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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