Lookup value in table with two criteria in rows

[ioncila]

Hi
I'm stucked with this issue today. Maybe it's the new years's eve hangover still.

In the attached file I have 2 sheets.
One has data with 5 columns for each month.
What I intend to do in the other sheet is to lookup for values in the first sheet related to the month I choose.

I have tried some combinations of lookup, match and index functions but I always get errors.

I would appreciate your help

Thank You very much

[Bob Phillips]

What are G, Gd, GC and so on?

[ioncila]

What are G, Gd, GC and so on?

Sorry
It's Task1, Task2,..., Task5

I forgot to substitute them.

[ioncila]

Here's the corrected file
I'm still trying index and match functions but can't figure the error.

Some help would be nice
Thanks in advance

[Nathan_Hale]

lookuptest.xlsx

Try this formula for Cell B6, Sheet "Mensal":

={INDEX(Folha2!$C$4:$BJ$99,0,MATCH(1,(Folha2!$C$2:$BJ$2=Mensal!$B$3)*(Folha2!$C$3:$BJ$3=Mensal!B$5),0))}

I added the 0, and the $ (bold in above formula).

NH

[ioncila]

lookuptest.xlsx

Try this formula for Cell B6, Sheet "Mensal":

={INDEX(Folha2!$C$4:$BJ$99,0,MATCH(1,(Folha2!$C$2:$BJ$2=Mensal!$B$3)*(Folha2!$C$3:$BJ$3=Mensal!B$5),0))}

I added the 0, and the $ (bold in above formula).

NH

Hi Nathan
Thank you for your help but formula don't return any result.
In the file you uploaded there's nothing neither.
Is there something missing?

Ioncila

[Bob Phillips]

Try this array formula

=INDEX(Folha2!$C$4:$BJ$99,ROW($A1),MATCH(1,(Folha2!$C$2:$BJ$2=Mensal!$B$3)* (Folha2!$C$3:$BJ$3=Mensal!B$5),0))

[ioncila]

Works perfectly
Thank you very much once again

There's a formula detail that I can't figure (the reference to ROW($A1)) but I'm going to search and study it.

Regards
Ioncila

[Bob Phillips]

It just gives you a row index into the INDEXed range, starting at 1, and incrementing as you copy down.

[ioncila]

It just gives you a row index into the INDEXed range, starting at 1, and incrementing as you copy down.

Thanks again
Ioncila

[Nathan_Hale]

Hi Nathan
Thank you for your help but formula don't return any result.
In the file you uploaded there's nothing neither.
Is there something missing?

Ioncila

Hi Ioncila,

Glad you figured it out. Sorry about the attachment. I am just getting started on this site. When I used the formula I posted it returned no result as well.... until I changed the month in the drop-down and then it showed a result. I didn't think there was a number to return for the default month. Anyway glad it worked out.

NH