For the 2nd instance...

Array entered** :

=INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last instance:

=LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12)

--
Biff
Microsoft Excel MVP


"Billy Leung" wrote in message
...
Hello,

I've a table below, I would like to setup a query which could return a
value
showing the 2nd match and last match in the next column. Thank in advance
for anyone great help.

cheers
Billy

Column B C D
1st 2nd
Row criteria criteria Value
3 A Y 1
4 B Y 2
5 C Y 3
6 A Y 4
7 B X 5
8 C Y 6
9 D X 7
10 A Y 8
11 B Y 9
12 C X 10

query 1 query 2
1st criteria A C
2nd criteria Y Y
nth of match 2 last

Result 4 6