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, etc. I've used conditional formatting to highlight the lowest per airline, now I want to display which airline has the lowest fare of all of them along with the fare. I have a min(all of the values) which displays the lowest fare of all airlines/fare options, but I also want to display the airline for that value. Using the cell that has the airline name is not easy as it's different in relation to the minimum fare depending on which one it is. I thought using the range name would be relatively easy. Any ideas or alternatives?
- 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.
