View original (French)

How to assign a value to a name in Excel?

Solved
cocodepeche Posted messages 3 Status Member -  
cocodepeche Posted messages 3 Status Member -
```html
Hello, In order to calculate travel expenses, I have recorded a list of cities and the associated distances. How can I automatically display the mileage in column B when I select a city from the dropdown list in column A? Thank you for your help.
```

1 answer

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    your database in Sheet1:

    _city in A, distance in B
    _edition in Sheet2

    if we start in A2
    dropdown menu city name in A
    km to display in B

    =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,0),"")

    the error handling code prevents the display of #N/A when A2 is empty, but is only applicable from Excel 2007

    if earlier:

    =IF(A2="","",VLOOKUP(A2,Sheet1!A:B,2,0))

    best regards

    --
    The quality of the answer mainly depends on the clarity of the question, thank you!
    1
    1. cocodepeche Posted messages 3 Status Member
       
      Thank you very much, it works! (I'm taking advantage: Can you explain the "2" and the "0"?) Kind regards.
      0
      1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453 > cocodepeche Posted messages 3 Status Member
         
        No problem

        The 2
        VLOOKUP searches for a value in the 1st column of a range and returns the value on the same row in the column indicated by the 2nd
        for example, if you have a range B:X, the formula will look for the value in column B and if you specify 2, it will return the value in C (2nd column of B:X) 4 the value in D, etc.
        0
        It is an Excel code that instructs the formula to find only exact matches. This can also be written as....;FALSE)
        without this information, VLOOKUP will select the nearest lower value, provided that the lookup column is sorted in ascending order (Alpha or numeric)

        and to complete the information: HLOOKUP does the same thing, but with rows.

        Best regards
        0
      2. cocodepeche Posted messages 3 Status Member > Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention  
         
        THANK YOU++++ it's bright!
        It offers a lot of possibilities and will be very useful to me.

        Have a great day.
        0