Simultaneously use VLOOKUP and HLOOKUP in EXCEL 2003

[Nicky]

I am trying to get data from a Sales Forecast into a planning sheet.

Dates are across the horizontal rows and product codes are on the vertical
columns

ie:
Sun Mon Tue Wedn
12-Oct 13-Oct 14-Oct 15-Oct

F13401 170 90 140 220
F13403 34 33 38 37
F13404 32 32 55 31
F13407 360 350 350 510

I want to return the value by looking up the date first and then looking for
the product code and entering the figure... so 13-oct for F13404 = 32.

I have tryed doing an @IF with VLOOKUP and HLOOKUP but it keeps failing.

Many thanks

 

[Mike H]

Hi,

This assumes your table is in a1 - E5 (A1 actually empty)

=SUMPRODUCT((A2:A5=G1)*(B1:E1=G2)*(B2:E5))

Where
G1 is the code you want
G2 is the date you want

Mike

 

[Roger Govier]

Hi Nicky

With your data held in A16, enter the Product code you want in F1 and the
date in G1 and use
=INDEX($A$1:$D$6,MATCH(F1,$A$1:$A$6,0),MATCH(G1,$A$2:$D$2,0))

 

[Nicky]

Hi Roger.

With a bit of tweeking for my workbook which is spread over several
worksheets it now is working a treat.

Many thanks