vlookup and hlookup?

  • Thread starter Thread starter Liz Taylor
  • Start date Start date
L

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
 
One way:

Assuming your table given is in Sheet1, A1:D3

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
 
Oops, the example table structure in Sheet2
came out "garbled" after posting.

It should look like this:

StockCode Feb Jun
01 0001
01 0000
 
Back
Top