# Help with multiple conditions with lookup table

1. ## Help with multiple conditions with lookup table

Hi there,

I am wondering if someone is able to help with a formula that requires multiple conditions on a lookup table ?

I have attached an example as I know the above description is vague.

Paul...

2. without looking at your file, assuming your two criteria are in columns a and b, and the table is 10 rows long, starting in row 1
criteria are in cells d1 and e1

=offset(c1,match(1,(a1:a10=d1)*(b1:b10=e1),0)-1,0)

entered with control+shift+enter

Duane,

4. Column H5

=INDEX(\$C\$5:\$C\$25,MATCH(G5,\$B\$5:\$B\$25,FALSE),MATCH(\$H\$4,\$A\$5:\$A\$25,FALSE))
=INDEX(\$C\$5:\$C\$25,MATCH(G6,\$B\$5:\$B\$25,0),MATCH(\$H\$4,\$A\$5:\$A\$25,0))
=INDEX(\$C\$5:\$C\$25,MATCH(G7,\$B\$5:\$B\$25,0),MATCH(\$H\$4,\$A\$5:\$A\$25,0))
=INDEX(\$C\$5:\$C\$25,MATCH(G8,\$B\$5:\$B\$25,0),MATCH(\$H\$4,\$A\$5:\$A\$25,0))
=INDEX(\$C\$5:\$C\$25,MATCH(G9,\$B\$5:\$B\$25,0),MATCH(\$H\$4,\$A\$5:\$A\$25,0))
=INDEX(\$C\$5:\$C\$25,MATCH(G10,\$B\$5:\$B\$25,0),MATCH(\$H\$4,\$A\$5:\$A\$25,0))

Column I5

=INDEX(\$C\$5:\$C\$25,MATCH(1,(G5=\$B\$5:\$B\$25)*(\$I\$4=\$A\$5:\$A\$25),0))
=INDEX(\$C\$5:\$C\$25,MATCH(1,(G6=\$B\$5:\$B\$25)*(\$I\$4=\$A\$5:\$A\$25),0))
=INDEX(\$C\$5:\$C\$25,MATCH(1,(G7=\$B\$5:\$B\$25)*(\$I\$4=\$A\$5:\$A\$25),0))
=INDEX(\$C\$5:\$C\$25,MATCH(1,(G8=\$B\$5:\$B\$25)*(\$I\$4=\$A\$5:\$A\$25),0))
=INDEX(\$C\$5:\$C\$25,MATCH(1,(G9=\$B\$5:\$B\$25)*(\$I\$4=\$A\$5:\$A\$25),0))
=INDEX(\$C\$5:\$C\$25,MATCH(1,(G10=\$B\$5:\$B\$25)*(\$I\$4=\$A\$5:\$A\$25),0))

you will have to use the iserror formula if you want to get rid of the NA

the formula in column I is an array formula you will have to hit ctrl shift enter

I don't know why but column I needed a different formula

5. ## Big Thankyou

Dave,

Thankyou very much for your help. I have been struggling with this for about a month. You have literally saved me days of work.

Thanks Champ

Paul...

6. ## Lookup Two Criteria using SUMPRODUCT

Sometimes I get stuck on the lookup formulas I forget that there is SUmproduct

this one works for both columns

``Please Login or Register  to view this content.``

7. ## Thankyou

Dave,

Thankyou for the continued thought, the second reply and thankyou for sharing your knowledge.

Now I have a working example I will be exploring the full range of possibilities that this formula and the previous one will bring.

I enjoy learning new things and I to enjoy sharing what I have learnt, mind you it is a very much lower level than yourself, but it is good to know that you can make someones day a little brighter by a simple formula.

Thanks once again

Paul...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1