Not sure what formula to use.... Index match, Vlookup or ?

SelinaR

Board Regular
Joined
Feb 2, 2012
Messages
65
Office Version
  1. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
I have a mileage chart that runs on an x and a y-axis and need to have a drop-down that picks up a TO and FROM but in the correct axis.

I have tried multiple options but because the data has two prices (city to country and country to the city) I can't find a solution. o_O

I have made a drop-down box for from and to but as there are two sets of data, how can I build a formula to pick up the correct axis.?

Thanks so much in advance for your help (y)
 

Attachments

  • MIL Formula.JPG
    MIL Formula.JPG
    82.7 KB · Views: 9
No, if you look at the example I posted, B4:M15 is what is referenced by the named range 'city'.

You can swap out each instance of city for $B$4:$M$15 and it will work the same.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
thanks everyone....

I think I should have been clearer in my original request.... the values from & to and then to & from are $ values to charge for hire car

- therefore if I use from and to there is 1 value and to & from is another

1599786980461.png
 
Upvote 0
Hiya - thanks for this one... I previously tried to use this option, but as the $ are different from and to and then to and from it wasn't accurate
That's what my suggestion gives you
+Fluff New.xlsm
ABCDEFGHIJKLMN
1
2FromAlbany350FromBunbury300
3ToBunburyToAlbany
4PERTH APOPERTH CITYFREEMANTLEOSBORNE PARKWELSHPOOLWANGARAREDCLIFFMIDLANDALBANYBROOMEBUNBURYCARNARVON
5PERTH APO855605801991705961155039368245
6PERTH CITY025495249111665150714794583763
7FREEMANTLE2594358067989963693636958728624
8OSBORNE PARK2515367715146197840474950332297
9WELSHPOOL25302966814717339633530346575360
10WANGARA259812059645636670707194472333
11REDCLIFF253677014229441188921734108340
12MIDLAND300398974491914970292124834438399
13ALBANY1000152357395608825285217119300839
14BROOME90367812951313376177851601358110
15BUNBURY75099679588814579672344350307804
16CARNARVON450783338355444428810557380560
Master
Cell Formulas
RangeFormula
D2D2=INDEX(C5:N16,MATCH(C3,B5:B16,0),MATCH(C2,C4:N4,0))
H2H2=INDEX(C5:N16,MATCH(G3,B5:B16,0),MATCH(G2,C4:N4,0))
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top