+ Reply to Thread
Results 1 to 10 of 10

Index, match and small (look up for differents rows based on one criteria)

  1. 07-29-2014, 05:57 AM #1
    giscosta_br
    giscosta_br is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Madrid
    MS-Off Ver
    15.0.4631.1002 (Hogar y Empresas 2013)
    Posts
    8

    Index, match and small (look up for differents rows based on one criteria)

    Dear All,

    I'm trying to lookup for an information from a database based on one condition, but the issue is that I need to extract more than one row (see file attached in spanish).

    See a video that may help (minute 18:36) http://www.youtube.com/watch?v=9jmNEqrfrZs

    A second problem would be to bring no information when the price is zero.

    Could you please let me know how can I solve these 2 issues?

    B. Regards,
    Gisele
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-29-2014, 06:31 AM #2
    Hawkeye16
    Hawkeye16 is offline
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Index, match and small (look up for differents rows based on one criteria)

    See attached, the second formula for price is a bit longer due to needing multiple criteria and checks.

    edit: If you need to update the length of the references (table of input gets longer) you will need to press Ctrl + Shift + Enter as these are array formulas. After computing one you can just copy and paste it to the other formula cells that are the same.
    Attached Files Attached Files
    Last edited by Hawkeye16; 07-29-2014 at 06:34 AM.
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!
    Reply With QuoteRegister To Reply
  3. 07-29-2014, 10:33 PM #3
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Index, match and small (look up for differents rows based on one criteria)

    Got the solution..

    See the attached file..


    I have used array formula..
    So use ctrl + shift + enter to enter the formula..
    Don't use only enter..


    Don't forget to click *
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 08-05-2014, 11:00 AM #4
    giscosta_br
    giscosta_br is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Madrid
    MS-Off Ver
    15.0.4631.1002 (Hogar y Empresas 2013)
    Posts
    8

    Re: Index, match and small (look up for differents rows based on one criteria)

    Hi Guys,

    I tried to update the length of the references (as the table of input got longer) but the formulas are not working.

    I had also create a macro to print it automatically in PDF individually but donīt think this is causing the problem because itīs working perfectelly until the customer 4 (the column A at the detail sheet is who defines everything that should be populated in the invoice sheet).

    See the file attached, I have changed the customers names and other info but it should be my final file..

    I would really appreciate if somebody is online and can help me to get it solved ASAP as I need to finish the invoices today...

    B. Regards,
    Gisele
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 08-06-2014, 07:41 AM #5
    Hawkeye16
    Hawkeye16 is offline
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Index, match and small (look up for differents rows based on one criteria)

    Can you attach as an excel workbook? My work computer doesn't even like macro workbooks, let alone zip files. If not perhaps Vikas can still help.
    Reply With QuoteRegister To Reply
  6. 08-06-2014, 08:52 AM #6
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Index, match and small (look up for differents rows based on one criteria)

    Hi Costa..
    I can help you ...
    But you have to wait for 1 hour..
    as I am in the office..
    and will solve your problem at home..

    Thanks..
    Regards,,
    Vikas Gautam
    Reply With QuoteRegister To Reply
  7. 08-06-2014, 12:44 PM #7
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Index, match and small (look up for differents rows based on one criteria)

    Got the solution..
    and this time even better..
    No Use of Array formula..

    Formula:-
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    If your wanna know and appreciate that how this formula works.. then visit..
    http://excel-buzz.blogspot.in/2014/0...-to-array.html

    check the attachment..


    Don't forget to click *
    Attached Files Attached Files
    Last edited by Vikas_Gautam; 08-06-2014 at 12:49 PM.
    Reply With QuoteRegister To Reply
  8. 08-08-2014, 08:58 AM #8
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Index, match and small (look up for differents rows based on one criteria)

    Alternative solution..

    NthMatch UDF Used..
    easy and simple to understand..

    wanna know how it works.. click the link below..
    http://excel-buzz.blogspot.in/2014/0...-to-array.html

    Check the attachment..

    Don't forget to click *
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 08-29-2014, 06:53 AM #9
    giscosta_br
    giscosta_br is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Madrid
    MS-Off Ver
    15.0.4631.1002 (Hogar y Empresas 2013)
    Posts
    8

    Re: Index, match and small (look up for differents rows based on one criteria)

    Dear Vikas, it is now working perfectly!!! Thanks a lot for all your help
    Just to let you know I used the first solution you gave me

    Would like also to thanks you Hawkeye for your attention.

    Sorry not answering before guys, I was on holidays
    Reply With QuoteRegister To Reply
  10. 08-29-2014, 09:42 PM #10
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Index, match and small (look up for differents rows based on one criteria)

    You are always welcome..Costa..
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. New & Lost: Multi Criteria & Multi Row INDEX, SMALL, MATCH...
    By morleyp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-23-2014, 12:40 PM
  2. [SOLVED] INDEX MATCH SMALL ROW, Double criteria Lookup
    By GP_SRT in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-22-2013, 11:15 AM
  3. [SOLVED] problem with INDEX([...],SMALL(IF[...],ROW to list results based on more than one criteria
    By cows in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-22-2013, 01:48 PM
  4. Index & Match Based on Criteria
    By cychua in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2013, 06:03 AM
  5. Index Match based on 2 Criteria only returning 1 match
    By stsanders22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 11:26 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1