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

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?

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

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
---

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


.

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


.