Closed Thread
Results 1 to 7 of 7

Help with multiple conditions with lookup table

  1. #1
    Registered User
    Join Date
    11-08-2006
    Posts
    4

    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.

    Thankyou in advance

    Paul...
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    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
    not a professional, just trying to assist.....

  3. #3
    Registered User
    Join Date
    11-08-2006
    Posts
    4

    Thankyou For Reply

    Duane,

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

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,934
    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-08-2006
    Posts
    4

    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. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,934

    Lookup Two Criteria using SUMPRODUCT

    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.
    Last edited by davesexcel; 11-09-2006 at 06:56 AM.

  7. #7
    Registered User
    Join Date
    11-08-2006
    Posts
    4

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

Closed Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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