Results 1 to 15 of 17
Thread: Lookup question (Office 2003)

20061124, 07:52 #1
 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

20061124, 09:34 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20061124, 10:22 #3
 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 cherrypick 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

20061124, 10:51 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Lookup question (Office 2003)
But do MATCH and INDEX work with noncontiguous ranges? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20061124, 10:53 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Lookup question (Office 2003)
If at all possible, reorganize your data so that they are contiguous. Otherwise, it becomes very complicated.

20061124, 12:36 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,444
 Thanks
 166
 Thanked 651 Times in 619 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

20061124, 12:47 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20061124, 12:48 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Lookup question (Office 2003)
Zeddy's reply in this thread inspired the attached approach, using array formulas.

20061124, 12:49 #9
 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.jkpads.com
Professional Office Developers Association

20061124, 13:17 #10
 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

20061124, 13:18 #11
 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

20061124, 13:28 #12
 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.jkpads.com
Professional Office Developers Association

20061127, 06:44 #13
 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!

20061127, 07:29 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20061127, 07:54 #15
 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]