Lookup question (Office 2003)

Topic

New Reply

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

Thanks in advance for the help!

kislany

Reply | Quote

Replies

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

In G2: =INDEX($B$1:$E$1,MATCH(F2,B2:E2,0))
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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

Nice one Hans

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

You see how important it is to provide all relevant information – leaving out important details can lead to incorrect solutions. Back to the original idea – I have inserted a row with “x”s to indicate which columns should be included in the calculations. This row can be hidden if you prefer. See attached version.

Reply | Quote

Hi,
If all the values you are not interested in are text, as in your example, then you can just use:
=MIN(B4:I4) in column K, and:
=INDEX(B$1:I$1,MATCH(K4,B4:I4,0)-1) in column L.
FWIW.

Reply | Quote

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

Reply | Quote

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,ARG22,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,ARG60))

Reply | Quote

But do MATCH and INDEX work with non-contiguous ranges?

Reply | Quote

Hi Hans,

Nope. I was just responding to the SUM question.

Reply | Quote