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

Simultaneously use VLOOKUP and HLOOKUP in EXCEL 2003
 

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

Simultaneously use VLOOKUP and HLOOKUP in EXCEL 2003
 

Hi Nicky

With your data held in A1:D6, 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

Simultaneously use VLOOKUP and HLOOKUP in EXCEL 2003
 

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 A1:D6, 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