Lookup Tables?

Posted by Ian Walters on January 21, 2002 10:05 PM

I am trying to find the column heading for a particular value in a range. I can't use "IF" as it would limit me to 7 columns but the LOOKUP functions all require the date to be sorted in order.

For example, You have a table of destinations and airline flight costs.

Destination BA UA Conti KLM
Bahrain 1000 1200 800 1300
Dubai 750 800 725 1000
London 900 750 800 1000
New York 800 850 750 1200

I can put in a column with the MIN function to find the lowest price for each destination, but if there are 100 airlines (columns) I would need to do a manual search for the cheapest one.

Any suggestions (solutions!) gratefully received.

Ian.

Posted by Aladin Akyurek on January 21, 2002 11:53 PM

Ian --

I'll assume that B1:F5 houses your sample data including the labels:

{"Destination","BA","UA","Conti","KLM";"Bahrain",1000,1200,800,1300;"Dubai",750,800,725,1000;"London",900,750,800,1000;"New York",800,850,750,1200}

In A1 enter: =MATCH(9.99999999999999E+307,2:2)

This formula establishes the column number of the last column in use. It requires that you don't have anything else after the last airline data.

In A3 enter: Dubai [ a destination of interest for which we want the cheapest airline ]

In A4 enter: =MIN(OFFSET($B$1,MATCH(A3,B:B,0)-1,0,1,$A$1))

In A5 enter: =INDEX(1:1,MATCH(A4,OFFSET($B$1,MATCH("Dubai",B:B,0)-1,0,1,$A$1),0)+1)

You'll see the following in A:

{6;
"";
"Dubai";
725;
"Conti"}

Note. There can be more than one airline that is cheapest. You'll get the first one from left to right. A tip: You can enter airlines according to a quality ranking. Column B for the best, column C the second best so on. This guarantees that the cheapest airline for a given destination will be the best among the cheapest (among the ones that offers the min price).

Aladin

=============

Posted by Ian Walters on January 22, 2002 12:59 AM

Thanks for the quick reply - I'll try it out this afternoon and let you know.

Thanks again
Ian.
(Dubai)