PDA

View Full Version : [SOLVED] Lookup value in table with two criteria in rows



ioncila
01-02-2014, 06:01 AM
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
01-02-2014, 06:20 AM
What are G, Gd, GC and so on?

ioncila
01-02-2014, 07:25 AM
What are G, Gd, GC and so on?

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

I forgot to substitute them.

ioncila
01-02-2014, 04:09 PM
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
01-03-2014, 01:40 PM
11037

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)*(Folha 2!$C$3:$BJ$3=Mensal!B$5),0))}

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

NH

ioncila
01-03-2014, 04:35 PM
11037

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)*(Folha 2!$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
01-03-2014, 04:57 PM
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
01-03-2014, 05:12 PM
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
01-03-2014, 05:31 PM
It just gives you a row index into the INDEXed range, starting at 1, and incrementing as you copy down.

ioncila
01-03-2014, 05:37 PM
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
01-04-2014, 08:22 PM
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