Simultaneously use VLOOKUP and HLOOKUP in EXCEL 2003

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

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

"Nicky" wrote:

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

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))

--
Regards
Roger Govier

"Nicky" wrote in message
...
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

Hi Roger.

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

Many thanks

"Roger Govier" wrote:

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))

--
Regards
Roger Govier

"Nicky" wrote in message
...
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