+ Reply to Thread
Results 1 to 7 of 7

Calculate based on list and associated cell

  1. #1
    Registered User
    Join Date
    05-19-2022
    Location
    Australia
    MS-Off Ver
    Microsoft 365 MSO (Version 2201)
    Posts
    3

    Calculate based on list and associated cell

    Hi all, I'm trying to do something but can't figure out a simple way to do it. I'm sure you will have some ideas!

    I have a list of vehicles, and each day they get inspected and the odometer readings (currents kilometers or KM Start) are recorded in column E. In column F, I would like to automatically display the kilometers remaining until the vehicle is due for a service, after I pick the vehicle from the drop down list on Column C and enter the KM Start readings in Column E.
    This would have to take information in the Unit No. cell (Column C) and the Service Due cells (E3 to E10) for that vehicle, then calculate the remaining kilometers (by taking the KM Start from the Service Due numbers).
    Have a look at the image below, and I have attached the spreadsheet for you to play with. You will see in cell F13 my attempt, which doesn't work for the list of vehicles just the one I have specified in the formula.

    Let me know if I need to explain my question more clearly.

    Thank you!

    1.JPG
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-12-2022
    Location
    Thialand
    MS-Off Ver
    Excel-2019
    Posts
    4

    Re: Calculate based on list and associated cell

    I'm not sure because I'm accually Thai not good in English but the results is it work for you?


    In Cell F13 =IFERROR(INDEX($E$3:$E$10,MATCH($C13,$B$3:$B$10,0))-$E13,"")
    Copy down

    In Cell G13 you can also automatic by this formular

    G13 =IFERROR(INDEX($F$3:$F$10,MATCH($C13,$B$3:$B$10,0)),"")
    Copy down
    Last edited by Xcelvba; 05-20-2022 at 02:41 AM.

  3. #3
    Registered User
    Join Date
    05-20-2022
    Location
    Utah, United States
    MS-Off Ver
    Office 365 Insider Beta
    Posts
    1

    Re: Calculate based on list and associated cell

    These are the ways I would do it:
    =IFERROR(XLOOKUP(C13,$B$3:$B$10,$E$3:$E$10)-E13,"Vehicle not found")
    =IFERROR(INDEX($E$3:$E$10,MATCH(C13,$B$3:$B$10,0))-E13,"Vehicle not found")

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Calculate based on list and associated cell

    Another option:

    =IFNA(VLOOKUP(C13,$B$3:$E$10,4,0)-E13,"No Vehicle Found")
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    05-19-2022
    Location
    Australia
    MS-Off Ver
    Microsoft 365 MSO (Version 2201)
    Posts
    3

    Re: Calculate based on list and associated cell

    Hi Xcelcba,

    Thanks for your suggestion! It was the first I tried and it worked perfectly.

  6. #6
    Registered User
    Join Date
    05-19-2022
    Location
    Australia
    MS-Off Ver
    Microsoft 365 MSO (Version 2201)
    Posts
    3

    Re: Calculate based on list and associated cell

    Thank you everyone for the tips!
    I tried the suggestion from Xcelvba and it worked, so I ran with it. (I will also add reputation.)

    - Nora.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Calculate based on list and associated cell

    Glad to have helped.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to calculate sales tax amount based on a fixed drop down list
    By Thomas.MAP123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-26-2022, 04:42 AM
  2. [SOLVED] what function to use to calculate a value based on drop down list
    By HelenaG in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-05-2022, 10:23 AM
  3. [SOLVED] Formula to Calculate Amount based on Drop List
    By Wit24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2021, 09:16 AM
  4. Spreadsheet that will calculate shopping list based on recipies
    By ea223 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-05-2015, 01:39 AM
  5. Calculate a date based on a drop down list
    By YNWA in forum Excel General
    Replies: 4
    Last Post: 06-08-2012, 08:17 PM
  6. Replies: 3
    Last Post: 05-09-2012, 01:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1