Help with multiple conditions with lookup table

Paul Harris · #1 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 · #2 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 · #3 11-08-2006, 10:56 PM

Duane,

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

**davesexcel** · #4 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 · #5 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** · #6 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

Code:

column H
=SUMPRODUCT(($B$5:$B$25=G5)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G6)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G7)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G8)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G9)*($A$5:$A$25=$H$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G10)*($A$5:$A$25=$H$4)*($C$5:$C$25))
column I
=SUMPRODUCT(($B$5:$B$25=G5)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G6)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G7)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G8)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G9)*($A$5:$A$25=$I$4)*($C$5:$C$25))
=SUMPRODUCT(($B$5:$B$25=G10)*($A$5:$A$25=$I$4)*($C$5:$C$25))

Paul Harris · #7 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...

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)