Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match question
I have a table that looks like this:
January February Currency 1 Text 1 1.1000 2.1000 Text 2 1.2000 2.2000 Text 3 1.3000 2.3000 Currency 2 Text 1 1.1200 2.1200 Text 2 1.2200 2.2200 Text 3 1.2300 2.2300 On sheet 2, I want cell B1 to return 2.2000 if I have Currency 1 in cell A1, Text 2 in cell A2, and February in cell A3. I tried index/match but I'm struggling due to the fact there are 3 parameters. Also, column A of the data table has blank cells. This table is emailed to me weekly and is rather large. Is there a way to make a formula work without having to fill in the blank cells in column A each time I receive it? Thanks, Claudia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match question
Try this index/match rendition which delivers it "as-is" (tested ok here)
It assumes your source table as posted is in sheet: x (just rename your source sheet as: x for easy paste/plug-in & play) where col A = Currency, col B = Text, Cols C across = numbers (Months: January, etc appears in C1 across) and that it's always 3 rows per currency In Sheet2, you have A1:A3 containing the inputs for Currency, Month and Text Place in B1, normal ENTER will do: =INDEX(OFFSET(INDIRECT("x!B"&MATCH(A1,x!A:A,0)),,M ATCH(A2,x!1:1,0)-2,3),MATCH(A3,OFFSET(INDIRECT("x!B"&MATCH(A1,x!A:A ,0)),,,3),0)) Success? celebrate it, hit the YES below -- Max Singapore --- "Claudia" wrote: I have a table that looks like this: January February Currency 1 Text 1 1.1000 2.1000 Text 2 1.2000 2.2000 Text 3 1.3000 2.3000 Currency 2 Text 1 1.1200 2.1200 Text 2 1.2200 2.2200 Text 3 1.2300 2.2300 On sheet 2, I want cell B1 to return 2.2000 if I have Currency 1 in cell A1, Text 2 in cell A2, and February in cell A3. I tried index/match but I'm struggling due to the fact there are 3 parameters. Also, column A of the data table has blank cells. This table is emailed to me weekly and is rather large. Is there a way to make a formula work without having to fill in the blank cells in column A each time I receive it? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match question
As long as the "text" categories are the same for each currency and they're
in the exact same sequence for each currency. Here's a small sample file that demonstrates this. It'd be easier to "see" the formula rather than trying to explain it (even though it's not very complicated). Claudia.xls 15kb http://cjoint.com/?csbytZdNn6 -- Biff Microsoft Excel MVP "Claudia" wrote in message ... I have a table that looks like this: January February Currency 1 Text 1 1.1000 2.1000 Text 2 1.2000 2.2000 Text 3 1.3000 2.3000 Currency 2 Text 1 1.1200 2.1200 Text 2 1.2200 2.2200 Text 3 1.2300 2.2300 On sheet 2, I want cell B1 to return 2.2000 if I have Currency 1 in cell A1, Text 2 in cell A2, and February in cell A3. I tried index/match but I'm struggling due to the fact there are 3 parameters. Also, column A of the data table has blank cells. This table is emailed to me weekly and is rather large. Is there a way to make a formula work without having to fill in the blank cells in column A each time I receive it? Thanks, Claudia |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match question
Clarification ...
In Sheet2, if you have A1:A3 containing the inputs for Currency, Text, Month (this is your actual order for the inputs. I had specifed it a little different earlier) use this in say, B2: =INDEX(OFFSET(INDIRECT("x!B"&MATCH(A1,x!A:A,0)),,M ATCH(A3,x!1:1,0)-2,3),MATCH(A2,OFFSET(INDIRECT("x!B"&MATCH(A1,x!A:A ,0)),,,3),0)) The advantage here is the flexibility for the Text param within each Currency "block" of 3 rows. The order/uniqueness is immaterial, the expression will still work correctly. -- Max Singapore --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match question
Thank you both. I tried both methods and they each worked. This is a big
time saver for me. "T. Valko" wrote: As long as the "text" categories are the same for each currency and they're in the exact same sequence for each currency. Here's a small sample file that demonstrates this. It'd be easier to "see" the formula rather than trying to explain it (even though it's not very complicated). Claudia.xls 15kb http://cjoint.com/?csbytZdNn6 -- Biff Microsoft Excel MVP "Claudia" wrote in message ... I have a table that looks like this: January February Currency 1 Text 1 1.1000 2.1000 Text 2 1.2000 2.2000 Text 3 1.3000 2.3000 Currency 2 Text 1 1.1200 2.1200 Text 2 1.2200 2.2200 Text 3 1.2300 2.2300 On sheet 2, I want cell B1 to return 2.2000 if I have Currency 1 in cell A1, Text 2 in cell A2, and February in cell A3. I tried index/match but I'm struggling due to the fact there are 3 parameters. Also, column A of the data table has blank cells. This table is emailed to me weekly and is rather large. Is there a way to make a formula work without having to fill in the blank cells in column A each time I receive it? Thanks, Claudia . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Claudia" wrote in message ... Thank you both. I tried both methods and they each worked. This is a big time saver for me. "T. Valko" wrote: As long as the "text" categories are the same for each currency and they're in the exact same sequence for each currency. Here's a small sample file that demonstrates this. It'd be easier to "see" the formula rather than trying to explain it (even though it's not very complicated). Claudia.xls 15kb http://cjoint.com/?csbytZdNn6 -- Biff Microsoft Excel MVP "Claudia" wrote in message ... I have a table that looks like this: January February Currency 1 Text 1 1.1000 2.1000 Text 2 1.2000 2.2000 Text 3 1.3000 2.3000 Currency 2 Text 1 1.1200 2.1200 Text 2 1.2200 2.2200 Text 3 1.2300 2.2300 On sheet 2, I want cell B1 to return 2.2000 if I have Currency 1 in cell A1, Text 2 in cell A2, and February in cell A3. I tried index/match but I'm struggling due to the fact there are 3 parameters. Also, column A of the data table has blank cells. This table is emailed to me weekly and is rather large. Is there a way to make a formula work without having to fill in the blank cells in column A each time I receive it? Thanks, Claudia . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max index / match question | Excel Discussion (Misc queries) | |||
Index & Match Question | Excel Worksheet Functions | |||
Index / Match Question | Excel Worksheet Functions | |||
Index/Match question Need help! | Excel Worksheet Functions |