Vlookup yet again

[orion1951]

Hi,

I have a spreadsheet with 5000 rows and 15. The 1st Column either has a Y or N in it and the 12 column either has a 'South' or "West" in it.

What I would like to do o a different sheet is display only the rows that have a Y in column 1 and South in column 12. I would also like to only select a few columns to display on the presentation sheet. I know that a filter would work but I thought a lookup would be better. Any suggestions?.

Thx,

John

 

[Joe4]

Welcome to the Board!

VLOOKUP can only match on one field, not two, and can only return one match (the first one it finds).
So actually I think a filter would be better here.
With Advanced Filter, you can filter the results to a new location.

 

[Aryatect]

Hi,

If you absolutely have to use a formula then below will work for you. You will have to copy this across 15 columns and down to 5000 rows (to be safe). Assumption is that your data is in Sheet7:

Book1
	A	B
1	Y	South
2	Y	South
3	Y	South
4	Y	South
5
6
7
8
9
10
11
12
Sheet8

Cell Formulas
Range	Formula
A1	{=IFERROR(INDEX(Sheet7!A$1:A$11,SMALL(IF((Sheet7!$A$1:$A$11="Y")*(Sheet7!$L$1:$L$11="South"),ROW(Sheet7!$A$1:$A$11)),ROWS(A$1:A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[SpillerBD]

Two other options.
1. You can create a Pivot Table with the desired Table Columns in the Pivot Tables "Row" area. Use Tabular display, NO Subtotals and Repeat ALL Row labels.
2. You can create a Get and Transform Query and load the data to a new Table.

Both cases will need the Refresh run when changes are made on the original table, which can be set to occur when the Workbook opens.