how to use vlookup to find more than one criteria

[puriyves]

Dear Excel Expert,

I have a few question about processing data in excel, but I got no idea how to use VLOOKUP function for more than one criteria,
here is the example :
I got this data

DATES...QTY1....STAT1...QTY2....STAT2...QTY3...STAT3....QTY4....STAT4...QTY5....STAT5
---------------------------------------------------------------------------------------
3.......1.......A.......1.......B.......1.......C........1......D.......1........E
5.......2.......F.......1.......G.......0................0..............0........
7.......2.......A.......1.......C.......3.......D........0..............0........

zero value on QTY always have null value on STAT

I need the result like this,

......DATES.........1.......2.......3.......4.......5.......6.......7
------------------------------------------------------------------------
A...................0.......0.......1.......0.......0.......0.......2
B...................0.......0.......1.......0.......0.......0.......0
C...................0.......0.......1.......0.......0.......0.......1
D...................0.......0.......1.......0.......0.......0.......3
E...................0.......0.......1.......0.......0.......0.......0
F...................0.......0.......0.......0.......2.......0.......0
G...................0.......0.......0.......0.......1.......0.......0

can somebody give me the idea how to use VLookup function to process the data ?
sorry for my bad english

Thank You
Puriyves

[Lotus123]

A combination of MATCH and OFFSET will accomplish what you need (VLOOKUP simply won't work here). The solution formulas are fairly complex so I have uploaded a spreadsheet that uses your starting information to produce your requested output.

For reference here is the base formula so other experts can see what I did:
=IF(ISNA(OFFSET($A$1,MATCH(B$7,$A$2:$A$4,FALSE),MATCH($A8,OFFSET($A$1,MATCH(B$7,$A$2:$A$4,FALSE),0,1,11),FALSE)-2)),0,OFFSET($A$1,MATCH(B$7,$A$2:$A$4,FALSE),MATCH($A8,OFFSET($A$1,MATCH(B$7,$A$2:$A$4,FALSE),0,1,11),FALSE)-2))

I'll strip out the ISNA error checking part to make it a little easier to read:
=OFFSET($A$1,MATCH(B$7,$A$2:$A$4,FALSE),MATCH($A8,OFFSET($A$1,MATCH(B$7,$A$2:$A$4,FALSE),0,1,11),FALSE)-2)

[puriyves]

Wow, amazing, I don't even think that's so complex formula,
btw Thank You Lotus123, this will be very great reference for me to analyze and continue my report.

Puriyves