+ Reply to Thread
Results 1 to 9 of 9

Index(Match Problem

  1. #1
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Index(Match Problem

    Hi guys,

    Wondering if anyone can point me in the right direction please....

    Problem:

    I have item numbers listed in column "E"
    these items are located @ "A1" dropdown menu Venue1 & Venue2
    In column G I have the formula as =IF(A1="Venue1",VLOOKUP(E2,A10:B18,2,FALSE)),IF(A1="Venue2",VLOOKUP(E2,A1:B9,2,FALSE))
    which is meant to check on the sales percentages as per each section.



    I keep getting an error, the attached spreadsheet should narrate it better if my dribble has not lol,
    What is it I'm not understanding or doing wrong within the formula or cannot even be done?

    Using eXcel'16

    Any help,
    Much appreciated,
    Chad
    Attached Files Attached Files
    Last edited by chad328; 08-26-2016 at 06:59 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,869

    Re: Index(Match Problem

    Try this in G2 copied down:

    =IF(A$1="Venue1",VLOOKUP(E2,$A$2:$B$9,2,FALSE),IF(A$1="Venue2",VLOOKUP(E2,$A$11:$B$18,2,FALSE),""))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Index(Match Problem

    1) Your data ranges are in the wrong order.
    IF A1="Venue1" then you want the range from A2:B9 not A10:B18
    Same goes for IFA1="Venue2"

    2) The IFs are NOT nested. Youve ended the first IF before the second one starts hence the VALUE error,
    It should be

    =IF(A1="Venue1",VLOOKUP(E2,A11:B18,2,FALSE),IF(A1="Venue2",VLOOKUP(E2,A2:B9,2,FALSE)))
    or given only those two venues
    =IF(A1="Venue1",VLOOKUP(E2,A11:B18,2,FALSE),VLOOKUP(E2,A2:B9,2,FALSE))

    or even
    =VLOOKUP(E2,INDIRECT(LOOKUP(A1,{"Venue1","Venue2"},{"A2:B9","A11:B18"})),2,FALSE)
    Last edited by Special-K; 08-26-2016 at 07:09 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: Index(Match Problem

    Thank you Ali & Special-K for responding with both valid remedies,
    an additional question, if I had say 50 venues would I have to continue adding venue after venue etc etc ?

    ie:=IF(A1="Venue1",VLOOKUP(E2,A11:B18,2,FALSE),IF(A1="Venue2",VLOOKUP(E2,A2:B9,2,FALSE),IF(A1="Venue3",VLOOKUP(E2,A2:B9,2,FALSE))))

    Cheers,
    Chad

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,869

    Re: Index(Match Problem

    No. You would change the layout of your source data, adding a column for the venue, and then you would do INDEX MATCH MATCH using the venue in A1 as a match criterion.

  6. #6
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: Index(Match Problem

    Thanks guys, Reps added!

  7. #7
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: Index(Match Problem

    How do you mean Ali, that lost me for sure...
    There are 60 venues in total

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Index(Match Problem

    with a pivot table.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,869

    Re: Index(Match Problem

    You'd lay it out like this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Venue2
    Item
    Sales
    2
    Venue1
    1
    12%
    1
    16%
    3
    Venue1
    2
    14%
    8
    11%
    4
    Venue1
    3
    13%
    7
    12%
    5
    Venue1
    4
    11%
    6
    14%
    6
    Venue1
    5
    10%
    7
    Venue1
    6
    11%
    8
    Venue1
    7
    12%
    9
    Venue1
    8
    17%
    10
    11
    Venue2
    1
    16%
    12
    Venue2
    2
    9%
    13
    Venue2
    3
    10%
    14
    Venue2
    4
    12%
    15
    Venue2
    5
    15%
    16
    Venue2
    6
    14%
    17
    Venue2
    7
    12%
    18
    Venue2
    8
    11%
    Sheet: Sheet1

    and then use this formula:

    Excel 2016 (Windows) 32 bit
    H
    2
    =SUMPRODUCT(($A$2:$A1000=$B$1)*($B$2:$B1000=F2),$C$2:$C1000)
    Sheet: Sheet1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formatting with INDEX MATCH MATCH Problem
    By SwissExcel in forum Excel General
    Replies: 1
    Last Post: 07-22-2015, 07:40 AM
  2. [SOLVED] Index Match Match - syntax problem
    By Dabooka in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2014, 09:05 AM
  3. INDEX and MATCH problem
    By flapface in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2014, 11:54 AM
  4. Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  5. [SOLVED] Index and match problem
    By desertshore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-02-2013, 06:57 PM
  6. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  7. Index/Match Problem
    By neomastino in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2007, 03:48 PM

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