Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup question (Office 2003)

    I have a table similar to this example (simplified):
    .................................................. ..B........................C...................... ......D..........................E................ .....................F............................ .................................................. ...........................G
    1..........................................Company 1..........Company 2.......Company 3..........Company x........................
    2.Prices for country 1............10...........................12...... .................14............................8.. ................................(formula: =MIN(B2,C2,D2,E2))...........(formula: =IF(F2=B2,$B$1,IF(F2=C2,$C$1)), etc
    3.Prices for country 2..............8...........................12..... ..................11............................10 ................................

    Currently with these two formulas in F2 and G2 I get: 8 Company x
    So far this works, however I know that we have only max 7 IF's to put in a formula, 's and I will have an increasing number of companies in my columns. Is there a better way to work through these two formulas - to get the best price (lowest) with the min formula and then write next to it the Company name, so we know which company offers the lowest price?

    I hope it's not very confusing [img]/forums/images/smilies/smile.gif[/img]

    Thanks in advance for the help!

    kislany

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup question (Office 2003)

    In F2: <code>=MIN(B2:E2)</code>
    That way, you don't have to enumerate the individual cells, just the first and last one with a colon in between.

    In G2: <code>=INDEX($B$1:$E$1,MATCH(F2,B2:E2,0))</code>
    The MATCH function looks up the value of F2 (the minimum) in B2:E2 and returns the position where it's found. The INDEX function returns the value in B1:E1 in the same position.
    Note: if there is more than one company with the same minimum value, the formula will return the first one.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup question (Office 2003)

    I can't do the first formula the way you mentioned, because I actually have a few other columns in between with other numbers (which I don't want to be calculated), but I've shows this basic example as illustration. So unfortunately I have to cherry-pick the cells that I'm using for the min.
    How would the second function work through my example, when the range is split, I tried it but I got an error at the match part.
    Thanks,
    kislany

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup question (Office 2003)

    But do MATCH and INDEX work with non-contiguous ranges? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup question (Office 2003)

    If at all possible, reorganize your data so that they are contiguous. Otherwise, it becomes very complicated.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Lookup question (Office 2003)

    Hi Hans

    Perhaps this could be done with array formulas?

    For example, if I wanted to find the lowest price in the row from a range of columns say B to P (but I want to exclude certain columns) I would:
    1. insert a row at the top (could be hidden later)
    2. In this row, I would enter a 1 in each column I want to include, and 1000 if I didn't want to include it
    3. The formula to find the lowest value in row 3 would be placed in Q3 would be {=MIN((B2:P2)*(B$1:P$1))} entered as an array formula.

    This works by 'arificially' pumping up the values in the columns I don't want by say, 1000.
    You could use conditional formatting to highlight the cell with the lowest value (e.g. green cell background).

    What do you think?

    zeddy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup question (Office 2003)

    Finding the minimum value in a discontiguous range is not a problem, I was worried about MATCH and LOOKUP. However, your suggestion gave me an idea, which I will post in a reply to Kislany. So thanks! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup question (Office 2003)

    Zeddy's reply in this thread inspired the attached approach, using array formulas.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup question (Office 2003)

    The SUM function accepts a max of 30 arguments. But you can fool Excel like this:

    =SUM((ARG1,ARG2,ARG3,ARG4,ARG5,ARG6,ARG7,ARG8,ARG9 ,ARG10,ARG11,ARG12,ARG13,
    ARG14,ARG15,ARG16,ARG17,ARG18,ARG19,ARG20,ARG21,AR G22,ARG23,ARG24,ARG25,ARG26,
    ARG27,ARG28,ARG29,ARG30),(ARG31,ARG32,ARG33,ARG34, ARG35,ARG36,ARG37,ARG38,
    ARG39,ARG40,ARG41,ARG42,ARG43, ARG44,ARG45,ARG46,ARG47,ARG48,ARG49,ARG50,ARG51,
    ARG52,ARG53,ARG54,ARG55,ARG56,ARG57,ARG58,ARG59,AR G60))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup question (Office 2003)

    The attached sample provides a solution based on the formulae in Hans' first response that doesn't require contiguous prices.
    Regards
    Don

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup question (Office 2003)

    Nice one Hans
    Regards
    Don

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup question (Office 2003)

    Hi Hans,

    Nope. I was just responding to the SUM question.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup question (Office 2003)

    I've tried to work through the formula, but I keep getting 0 instead of the actual company name, can you please have a look at at the attached Excel file for the match formula? T he file is a small version of the report I'm working on (with altered information, but the rows and columns layout is the same).
    Thanks!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup question (Office 2003)

    Your placement of Company 1 is inconsistent - it should be in B1:C1 instead of A1:B1.
    I have attached your workbook with a version of the formulas from my previous reply adapted for your situation.

  15. #15
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup question (Office 2003)

    I tried your file but it's not working for me. I am not sure why there is the 'if' in the formula for the row 2 to be empty. It is actually not empty and I have to add other things to those headings in row to as well - and this is where the formula goes wrong. Can you please have a look?

    Thanks for your help as usual [img]/forums/images/smilies/smile.gif[/img]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •