+ Reply to Thread
Results 1 to 6 of 6

Lookup value based on 2 condition and return all value from small to large

  1. #1
    Registered User
    Join Date
    01-11-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    9

    Lookup value based on 2 condition and return all value from small to large

    I have Drawing ID in Column A, Material No in Column B, PO Nos in Column C.
    I would like to get all PO numbers in Column E,F,G,H and so on.

    I tried the below formula but does not get the right answer.

    =(INDEX($A$1:$C$5,SMALL(IF($A$2:$B$2=$A$1:$B$5,ROW($A$1:$B$5)),COLUMN(A1)),3))

    Need your help.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup value based on 2 condition and return all value from small to large

    Did you enter that using CTRL SHIFT ENTER, and not just enter? That is an ARRAY formula
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Lookup value based on 2 condition and return all value from small to large

    Your explanation wasn't very clear; so I'm not sure if this is what you wanted. It returns the PO numbers of all orders which have the Draw ID that's listed in J1 and the material that's listed in J2. If this wasn't what you wanted, please try to explain again...

    This is an array formula and needs to be set with CTRL + SHIFT + ENTER
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 12-13-2014 at 12:53 PM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    01-11-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Lookup value based on 2 condition and return all value from small to large

    Sorry, I didn't explain properly.
    I use Ctrl+Shift+Enter for array formula.
    I want to get the all PO Numbers in a same row at different column for a condition matching DRAW ID & MATERIAL NUM.
    In other words, For given matching DRAW ID & Material Num, how many PO issued?
    In the sample I have 5 rows of data but in actual file I have 3000 rows of data.
    I have attached the file with expected result as well.
    Pls help.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Lookup value based on 2 condition and return all value from small to large

    Hi the formula was EXACTLY what you wanted, but your helpful explanation made it possible for me to get it exactly to your requirement(I hope!!).
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 12-14-2014 at 04:28 AM.

  6. #6
    Registered User
    Join Date
    01-11-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Lookup value based on 2 condition and return all value from small to large

    Dear Glenn Kennedy,
    Thanks. Looks OK, however I will test it at office tomorrow and update you..
    Once again thank you.

+ 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. [SOLVED] Failed Lookup based on small function
    By MattRNR in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2013, 09:03 PM
  2. Need to extract a small range from a large one based on cell value
    By Ahmed Abodorra in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-17-2013, 09:20 PM
  3. Replies: 5
    Last Post: 01-20-2012, 12:58 PM
  4. Replies: 1
    Last Post: 09-04-2006, 11:35 PM
  5. Compound Lookup, Large/Small, Match...
    By uw805 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2006, 08:50 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