+ Reply to Thread
Results 1 to 5 of 5

LookUP formula matching muitiple conditions

  1. #1
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    LookUP formula matching muitiple conditions

    Hello,

    Please refer attached work sheet and suggest a formula to look up discount %s matching multiple values.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: LookUP formula matching muitiple conditions

    =SUMPRODUCT(('Look UP'!$A$2:$A$30=A2)*('Look UP'!$C$2:$C$30=B2)*('Look UP'!$D$2:$D$30=C2)*('Look UP'!$E$2:$E$30)) ??
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: LookUP formula matching muitiple conditions

    hi

    Nice question. Hope my solution will hel you.

    d2 cell =IFERROR(VLOOKUP(C2,IF((A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13),'Look UP'!$D$2:$E$13,""),2,TRUE),0) Ctrl+Shift+Enter

    If you use excel 2003 version then use above formula
    =IF(ISERROR(VLOOKUP(C2,IF((A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13),'Look UP'!$D$2:$E$13,""),2,TRUE)),0,VLOOKUP(C2,IF((A2='Look UP'!$A$2:$A$13)*(Result!B2='Look UP'!$C$2:$C$13),'Look UP'!$D$2:$E$13,""),2,TRUE))

    Units sold column in lookup tabe should be sorted from smalles to largest in order to get the right percentage

    Uploaded file.
    Appreciate the help? CLICK *

  4. #4
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    Re: LookUP formula matching muitiple conditions

    THanks Fotis and AZ excel for your valuable time.

    Hi Fotis, that formula does work if there is an exact match, but please see my attached spreadsheet for the result i require

    Hi AZ, I opened your attacment and saw that i t was working, but when i did that on my file, it did not work. please see attached spreadsheet. also please explain the logic of sorting from smallest to largest
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    06-26-2012
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    274

    Re: LookUP formula matching muitiple conditions

    Sorry AZ, it was my mistake. That formula works well now. Excellent . THanks heaps.

+ Reply to 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