+ Reply to Thread
Results 1 to 4 of 4

All matches listed for data validation list (vlookup or index/match problem?)

  1. #1
    Registered User
    Join Date
    08-03-2020
    Location
    Marion, IN
    MS-Off Ver
    2016
    Posts
    3

    All matches listed for data validation list (vlookup or index/match problem?)

    I could use some similar help with this as I have tried but there are some things not working still. it might because of my A2

    I have a spreadsheet made up for trips listing companies, codes, hotels, rates, etc.
    What I am trying to do is select the code and then have the spreadsheet populate all hotels for that coded company.

    I am using in A2 a data validation listing of all codes, so I can drop down the list and select the code. The greatest number of hotels for a company is 7.

    My Example
    A B C D
    001 NWP EXTENDED STAY $140.00
    001 NWP HOLIDAY INN $140.00
    001 NWP LAQUINTA $129.00
    017 AC HAMPTON INN $109.00
    017 AC HOLIDAY INN $89.10
    017 AC COUNTRY INN $123.49
    019 AFS BW+ $120.00
    019 AFS FAIRFIELD INN $104.00
    030 BBB COURTYARD $100.00
    047 TAZ COMFORT STES $101.00
    047 TAZ QUALITY INN $84.15
    047 TAZ HOLIDAY INN $80.00

    What I want to return in my results is after I select from my A1 = Data Validation list of say, 017, it would look like this

    A B C D
    CODE CO HOTEL RATES
    A2=017 AC HAMPTON INN $109.00
    2 HOLIDAY INN $89.10
    3 COUNTRY INN $123.49
    4 #NA #NA
    5 #NA #NA
    6 #NA #NA
    7 #NA #NA

    I have used vlookup for rows 1-7, but it is always grabbing the next code/company, so there is a match problem; i have also used index and match, but the same is happening.

    I think I am on the right track, but again, in the example above, rows 4-7 are still bringing in data instead of #NA.

    Thoughts?

    using
    =VLOOKUP($A2,DATA!$A$2:$K$320,4, FALSE) = where DATA! is the next sheet with all of the er data
    =INDEX(DATA!A2:$K$320,MATCH(A2,DATA!A2:A320,0)+1,4)
    =INDEX(DATA!A2:$K$320,MATCH(A2,DATA!A2:A320,0)+6,4)


    THANKS FOR THE LOOK SEE!

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,512

    Re: All matches listed for data validation list (vlookup or index/match problem?)

    G
    H
    I
    J
    K
    L
    1
    choose code
    017
    2
    AC HAMPTON INN $109.00
    3
    AC HOLIDAY INN $89.10
    4
    AC COUNTRY INN $123.49

    G
    H
    I
    J
    K
    L
    1
    choose code
    017
    2
    =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"") =IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"") =IFERROR(INDEX(D:D,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"")
    3
    =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"") =IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"") =IFERROR(INDEX(D:D,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"")
    4
    =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"") =IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"") =IFERROR(INDEX(D:D,SMALL(IF($A$2:$A$100=$H$1,ROW($A$2:$A$100),10^10),ROW()-1)),"")
    Attached Files Attached Files
    Last edited by KOKOSEK; 08-04-2020 at 08:04 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    08-03-2020
    Location
    Marion, IN
    MS-Off Ver
    2016
    Posts
    3

    Re: All matches listed for data validation list (vlookup or index/match problem?)

    Okay, so there was a secret to how to engage this; i had never used ctrl+shift+enter magical buttons before in an array to make it work; everything typed in on my own spreadsheet but it is was not working.
    I then assumed it has something to do with the {} that appear on yours and not on mine. So I looked that up and waved my Harry Potter wand and press the three magic buttons and voila! The companies began showing up as ordered.
    Thank you for the assist on this one KOKOSEK.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,512

    Re: All matches listed for data validation list (vlookup or index/match problem?)

    Sorry I forgot to say that is array function and you have to accept it with Ctrl+Shift+Enter.
    So additionally you've got extra info about array functions :-)

    Happy to help.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

+ 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. Vlookup and Index Match Problem with Data Validation
    By Irish15 in forum Excel General
    Replies: 10
    Last Post: 04-08-2020, 06:22 AM
  2. [SOLVED] INDEX MATCH in a List Data Validation
    By alaramee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2018, 01:21 PM
  3. Index-Match Data Validation List not working
    By totally_lost in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-16-2014, 09:27 AM
  4. [SOLVED] Index-Match Data Validation List
    By DinghoAteMyBaby in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-22-2013, 10:42 AM
  5. Data Validation List Using Index & Match Formula
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-16-2012, 03:08 AM
  6. [SOLVED] Index Match - Data Validation List - Result N/A
    By Veloso in forum Excel General
    Replies: 4
    Last Post: 07-21-2012, 12:49 PM
  7. Vlookup or Index Match on 2 matches
    By jmuise2007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2007, 08:22 PM

Tags for this Thread

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