# Thread: Lookup value in table with two criteria in rows

1. ## Lookup value in table with two criteria in rows

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

2. What are G, Gd, GC and so on?

3. Originally Posted by xld
What are G, Gd, GC and so on?
Sorry
It's Task1, Task2,..., Task5

I forgot to substitute them.

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

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

6. Originally Posted by 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
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

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

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

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

10. Originally Posted by xld
It just gives you a row index into the INDEXed range, starting at 1, and incrementing as you copy down.
Thanks again
Ioncila

11. Originally Posted by ioncila
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•