{S:X;X;0;0;The Fireworkz lookup(,,) function{ITR-X}by Gerald L Fitton}
{S:X;X;0;1;It was Denis Murray-Smith who first put to me the problem of finding the column heading of the column containing the minimum value for that row. His problem was that of finding the best shop in which to buy a particular item when you know the prices of the items in each of many shops. The general problem is of wider application, for example, you may wish to find out which of many subjects are best for each pupil and then go on to find out which subject is best received! One of my correspondents now uses a slight variant of this application to check which of many types of investment is 'best' for her.}
{S:X;X;0;2;In the Novemberá1993 edition of Archive I gave details of my PipeDream 'solution' to this problem. I expected to 'port' that solution to Fireworkz but, when I 'ported' the PipeDream file to Versioná1.07 of Fireworkz it didn't work for the reason I describe below.}
{S:X;X;0;3;Although I have received a score of letters asking for details of a similar application for use with Fireworkz I have delayed describing a 'work around' believing that Versioná1.08 was 'just around the corner' and that the PipeDream application would 'port' across to Versioná1.08 and 'work' first time. However, so many of you have asked me how to get lookup(,,) to 'work' in Vá1.07 that I have decided to explain the 'work around'.}
{S:X;X;0;4;The screenshot below, figureá1, shows a simple spreadsheet which I've called [Shopping]. The names of the products appear in column a as Alpha, Beta, Gamma and Delta. The names of the shops appear in row 6 as Shopá1, Shopá2, Shopá3 and Shopá4. Of course you could have many more items and a much greater choice of shops.}
{S:X;X;0;5;Data (prices) is entered in the block b8e11. Column f contains a formula which calculates the cheapest price for the item in each row. Column g contains a formula which returns the name of the shop having the lowest price.}
{S:X;X;0;6;The price of each of the four items in each of the four shops appears in the range of slots b8e11. The formula in slot f8 is min(b8e8), and it returns the lowest price in row 8.}
{S:X;X;0;9;The difference between the Fireworkz formula and the pipeDream formula is the use of the transpose() function.}
{S:X;X;0;10;The arguments of the lookup(,,) formula are:}
{S:X;X;0;11;f8 \ù the key field containing the minimum price}
{S:X;X;0;12;b8e8 \ù the range in which you hope to find f8 (the minimum price)}
{S:X;X;0;13;b$6e$6 \ù the range which contains the value to be returned (the shop).}
{S:X;X;0;14;Note the $ characters in this last range\\\; these ensue that, as you replicate the formula from g8 down through the column g (see below), the final argument of the lookup(,,) function does not change.}
{S:X;X;0;15;For example, in the slot g11 you will find lookup(f11,transpose(b8b11),transpose(b$6e$6)).}
{S:X;X;0;16;When you 'port' the PipeDream version (which doesn't have the transpose function) to Fireworkz it doesn't work. It seems that, in Fireworkz, lookup(,,) works only with \Évertical\æ arrays. The 'work around' is to include the transpose() function as I have in this example.}
{S:X;X;0;17;The formulae in f8 and g8 have been replicated down the two columns using the following technique. Place the cursor in f8 and drag from f8 to g11 to mark the block f8g11. Click on the fill down button which is at the top left of the first button bar. You can extend the formulae down as many rows as you wish.}
