Lookup value help

[mik_da_man]

Hey guys,

I have a spreadsheet similar to the one attached and i need a formula
that will populate values on one sheet from the other sheet
based on a month choosen from a list.

It is probably simple, but the only problem is that the format of the
figures in the sheets cannot be changed

i'm stumped at the mo

All help appriciated

Cheers

Mik


+-------------------------------------------------------------------+
|Filename: problem.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4303 |
+-------------------------------------------------------------------+

 

[Don Guillett]

Sorry, this ng does not accept attachments. You must FULLY describe your
problem with clear explanations so that we don't have to read your mind.

 

[mik_da_man]

ng??

I thought i uploaded a zip file fine?

Anyway here is my problem

Sheet2 =


Jan Feb Mar Apr May


Belgium A 0.46 0.57 0.33 0.81 0.46
B 0.85 0.48 0.30 0.94 0.78

PortugalA 0.35 0.33 0.25 0.47 0.83
B 0.93 0.58 0.85 0.96 0.41

France A 0.84 0.32 0.28 0.72 0.63
B 0.67 0.12 0.27 0.24 0.51



Sheet 1 =

Drop down list of months


A B Total
Belgium 0
Portugal 0
France 0
Germany 0
Ireland 0


Kinda hard to expalin without an attachment

 

[pinmaster]

Try something like:

assuming on Sheet 2 you have.. A2:A9= country list, B2:B9= A or B, C1:G1=
months, C2:G9 range with values to lookup.

=INDEX(Sheet2!$C$2:$G$9,MATCH(I9,Sheet2!$A$2:$A$9,0),MATCH($J$8,Sheet2!$C$1:$G$1,0))
for A and:
=OFFSET(INDEX(Sheet2!$C$2:$G$9,MATCH(I9,Sheet2!$A$2:$A$9,0),MATCH($J$8,Sheet2!$C$1:$G$1,0)),1,0)
for B

HTH
JG

 

[pinmaster]

Sorry...couldn't open your attachment from the other newsgroup.

Here are the actual formulae base on your attachment:

=INDEX(Sheet2!$C$8:$N$21,MATCH($A6,Sheet2!$A$8:$A$21,0),MATCH(Sheet1!$A$2,Sheet2!$C$5:$N$5,0))
for A..copy down..and for B
=OFFSET(INDEX(Sheet2!$C$8:$N$21,MATCH($A6,Sheet2!$A$8:$A$21,0),MATCH(Sheet1!$A$2,Sheet2!$C$5:$N$5,0)),1,0)
copy down

HTH
JG

 

[mik_da_man]

Pinmaster,

That worked a treat

Thanks

Mik