+ Reply to Thread
Results 1 to 11 of 11

vlookup? match? index? MULTIPLE criteria for vlookup search problem....

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Hi All,

    I am having difficulty getting a formula to populate my lists

    I would like the table to populate depending on user input, do i need to create new more distinct columns?

    Or can i use a vlookup (without vba) with match, index? not quite sure how to get it right, been playing around for a while

    Cheers for any help.

    I have uploaded the book...Cells C1 and C2 are lists for user input

    Any ideas ohh powerful and knowledgeable Excel wizards?

    Andrew
    Attached Files Attached Files

  2. #2
    Forum Expert 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: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Try in D5 this ARRAY formula.

    =IFERROR(INDEX(S$2:S$6000,SMALL(IF($P$2:$P$6000&$Q$2:$Q$6000=$C$1&$C$2,ROW(S$2:S$6000)-1),ROW(S1))),"")

    Copy down and across.
    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
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Hi,

    Thanks so far... nearly works, however i need to use WEEK to match in both A column and R column... Do i make sense?

  4. #4
    Forum Expert 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: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Maybe?

    =IFERROR(INDEX(S$2:S$6000,SMALL(IF($P$2:$P$6000&$Q$2:$Q$6000&$R$2:$R$6000=$C$1&$C$2&$A5,ROW(S$2:S$6000)-1),ROW(S1))),"")

  5. #5
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Hi, I don;t believe its working,

    Essentially i need criteria chosen in c1 and c2 to match what the user has entered + also link up with r column which is the week in years.

    so if one choses e.g. belarus brand 1 then, should populate the corresponding columns for week 1 with week 1 data for belarus brand 1...

    do i make sense? :S

  6. #6
    Forum Expert 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: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    And my second formula, does not do this?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Hey, Yes it works for top row!! Thanks but how to a extend it to second row?

    Cheers

    Andrew

  8. #8
    Forum Expert 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: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Ok Andrew. Let's try this.

    =IFERROR(INDEX(S$2:S$6000,SMALL(IF($P$2:$P$6000&$Q$2:$Q$6000&$R$2:$R$6000=$C$1&$C$2&$A5,ROW(S$2:S$6000)-1),ROW(S$1))),"")

  9. #9
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Fantastic, your a star!

  10. #10
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    Solved!

    Thanks again fotis¬!

  11. #11
    Forum Expert 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: vlookup? match? index? MULTIPLE criteria for vlookup search problem....

    You are welcome.

    Thanks for the kind feedback and also for the reb*

+ 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