Forum Discussion
Display the name for a range given a formula that selects 1 value from the named range
I'm trying to make a simple low cost selection table for airline tickets. I have multiple named ranges for the lowest prices per airline, so Air1 will have 5 different fare options, Air2 will have 5...
Post few sample data and show your desired output.
| Westjet | Porter | |||||||||
| Package Type | a | b | c | d | ||||||
| Package Cost | $ 665.00 | $ 56.00 | $ 88.00 | $ 900.00 | al a carte | $ 6,665.00 | $ 6,665.00 | $ 6,665.00 | $ 6,665.00 | |
| Airfare | $ 500.00 | $ 3,000.00 | ||||||||
| Food | 50 | |||||||||
| Drinks | 50 | |||||||||
| Checked bags | 50 | |||||||||
| Seat selection | 50 | |||||||||
| Carry on | 50 | |||||||||
| Total: | $ 750.00 | $ 665.00 | $ 56.00 | $ 88.00 | $ 900.00 | $ 3,000.00 | $ 6,665.00 | $ 6,665.00 | $ 6,665.00 | $ 6,665.00 |
| Best Price: | $ 56.00 |
So in the spreadsheet, for each airline I have conditional bottom 1 to highlight it. The last line is min of all airline rates, I also want to print/display the name of the airline that has the lowest rate. I named each range by the airline, so the first 5 total cells are named "WestJet" for example.
- That didn't work, returns "0". But it helped me narrow down my issue. min() returns the minimum value of an array, I need the cell address with the min value, so in the example, I use min to show "best price", I would like to get that cell address and then I could then use offset to get the package type and airline. I can't seem to find that kind of function, they all return values as far as I can see, not cell references.
Hmmm...
