vlookup and hlookup?

[Liz Taylor]

Hello,

My spreadsheet looks something like this:

A B C D
1 StockCode Jan Feb Mar
2 01 0000 $10 $0 $50
3 01 0001 $20 $19 $35

I need to populate another table with data from this table and I thought I
might be able to use a combination of vlookup and hlookup but am not sure
how to go about it.

Let say the data I'm looking for is in C3; Stockcode 01 0001 and Feb = $19.
I know how to use Vlookup to get to this number, but I also know that if I
would add a column, my column count would be off and it would return the
incorrect number. So, I was hoping there is a way to combine vlookup with
hlookup or something to that effect.

Any suggestions on how to go about this?

Many thanks,

Thanks,

Liz

 

[Max]

One way:

Assuming your table given is in Sheet1, A13

In Sheet2
------------
Suppose you have another table with
*identical structure* to populate,
ie with Stockcodes in col A, A2 down,
and Months in row1, B1 across, viz:


StockCode
Feb
Jun


01 0001




01 0000






Put in B2:
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,MATCH(B$1,Sheet1!$A$1:$D$1,0
)-1)

Format B2 as currency

B2 will return "$19",
ie what is Feb's value for stockcode 01 0001 (from Sheet1)

Copy B2 across and down to populate this table

 

[Max]

Oops, the example table structure in Sheet2
came out "garbled" after posting.

It should look like this:

StockCode Feb Jun
01 0001
01 0000