VLOOKUP with multiple conditions

[Guest]

Hi, I am looking to extract data from a report, what I need to do is view all
the instances where a certain part number appears, but I only want to view
those part numbers that match two criteria, example is below, say Sheet 1

A B C D E
1 Pen John Paid All Full
2 Pencil Ben NP None 0
3 Pen Peter Paid All Full
4 Pen Paul Paid Partial Half

From the above table I would want to extract the orders for Pens, but only
show the ones that are Paid ( col C ) and Full ( col E ). I want to see them
in this format in Sheet 2
A B C D E
1 Pen John Paid All Full
2 Pen Peter Paid All Full

 

[Ron Rosenfeld]

Hi, I am looking to extract data from a report, what I need to do is view all
the instances where a certain part number appears, but I only want to view
those part numbers that match two criteria, example is below, say Sheet 1

A B C D E
1 Pen John Paid All Full
2 Pencil Ben NP None 0
3 Pen Peter Paid All Full
4 Pen Paul Paid Partial Half

From the above table I would want to extract the orders for Pens, but only
show the ones that are Paid ( col C ) and Full ( col E ). I want to see them
in this format in Sheet 2
A B C D E
1 Pen John Paid All Full
2 Pen Peter Paid All Full

Your columns should have labels.
Select some cell in your table.

Then

Data/Auto Filter
Select the Down Arrow next to "A" and select Pen
Select the Down Arrow next to "C" and select Paid
Select the Down Arrow next to "E" and select Full

Edit/Copy

Select the upper left cell in your extract area. Make sure that there aren't
hidden rows below this cell which might exist as a result of your auto-filter.
In other words, if your table is in A1:A10, select some cell like G11, or a
cell on another worksheet.

Edit/Paste


--ron

 

[Guest]

Thanks ,,, but not what quite what I was looking for ,,,, is there a way to
extract the data without using Auto Filter ,,,,,

 

[Domenic]

Assuming that Sheet1!A2:E5 contains your source data, try the following
formula system...

On Sheet2...

A2: enter your first criterion, such as 'Pen'

B2: enter your second criterion, such as 'Paid'

C2: enter your third criterion, such as 'Full'

D1: enter 0 (zero)

D2, copied down:

=IF((Sheet1!A2<>"")*(Sheet1!A2=$A$2)*(Sheet1!C2=$B$2)*(Sheet1!E2=$C$2),LO
OKUP(9.99999999999999E+307,$D$11)+1,"")

E1:

=LOOKUP(9.99999999999999E+307,D)

F2, copied down:

=IF(ROW()-ROW(F$2)+1<=$E$1,MATCH(ROW()-ROW(F$2)+1,$D$2:$D$5,0),"")

G2, copied across and down:

=IF(N($F2),INDEX(Sheet1!A$2:A$5,$F2),"")

Change the criteria entered in A2, B2, and C2, accordingly. Also, if
you prefer, you can hide helper Columns D, E, and F.

Hope this helps!