Index/Match question

[Claudia]

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

 

[Max]

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)),,MATCH(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

 

[T. Valko]

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

 

[Max]

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)),,MATCH(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.

 

[Claudia]

Thank you both. I tried both methods and they each worked. This is a big
time saver for me.

 

[T. Valko]

You're welcome. Thanks for the feedback!