Help with multiple conditions with lookup table

**Paul Harris** · 11-08-2006, 08:38 PM

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.

Thankyou in advance

Paul...

**duane** · 11-08-2006, 10:26 PM

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

**Paul Harris** · 11-08-2006, 10:56 PM

Duane,

Thankyou for your reply but it unfortunately has only produced the dreaded #N/A result.

**davesexcel** · 11-08-2006, 11:12 PM

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

**Paul Harris** · 11-08-2006, 11:19 PM

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

**davesexcel** · 11-09-2006, 06:52 AM

Thanks for the reply,
I had forgotten about the best solution for this:
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.

**Paul Harris** · 11-09-2006, 09:19 PM

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