# Thread: Lookup question (Office 2003)

1. ## 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. ## 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. ## 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. ## 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. ## Re: Lookup question (Office 2003)

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

6. ## 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. ## 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. ## Re: Lookup question (Office 2003)

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

9. ## 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))

10. ## 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.

11. ## Re: Lookup question (Office 2003)

Nice one Hans

12. ## Re: Lookup question (Office 2003)

Hi Hans,

Nope. I was just responding to the SUM question.

13. ## 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. ## Re: Lookup question (Office 2003)

Your placement of Company 1 is inconsistent - it should be in B1:C1 instead of A1:B1.

15. ## 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 Last

#### Posting Permissions

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