Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value help
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 | +-------------------------------------------------------------------+ -- mik_da_man ------------------------------------------------------------------------ mik_da_man's Profile: http://www.excelforum.com/member.php...o&userid=31067 View this thread: http://www.excelforum.com/showthread...hreadid=507430 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value help
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 -- mik_da_man ------------------------------------------------------------------------ mik_da_man's Profile: http://www.excelforum.com/member.php...o&userid=31067 View this thread: http://www.excelforum.com/showthread...hreadid=507430 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value help
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 "mik_da_man" wrote: 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 -- mik_da_man ------------------------------------------------------------------------ mik_da_man's Profile: http://www.excelforum.com/member.php...o&userid=31067 View this thread: http://www.excelforum.com/showthread...hreadid=507430 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value help
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 -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=507430 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup value help
Pinmaster, That worked a treat Thanks Mik -- mik_da_man ------------------------------------------------------------------------ mik_da_man's Profile: http://www.excelforum.com/member.php...o&userid=31067 View this thread: http://www.excelforum.com/showthread...hreadid=507430 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup function help | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |