1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Own Vehicle numbers using lookup.

Discussion in 'Ask an Excel Question' started by Shabbo, Aug 9, 2018.

  1. Shabbo

    Shabbo Member

    Messages:
    206
    Dear Sir,

    I wanted help to find out list of our own vehicles from OWN VEHICLE sheet.

    My Sheet3 Column A that is account head should show as ExpsFuel if Vehilces from our Own Vehicles and if not from Own Vehicles then vendor name from Entrybook should come.

    Eg : I will apply lookup in Sheet3 Column A for Vehicle no : from Sheet3 Column B If that vehicle from Own Vehicles List then account head should show as ExpsFuel else vendor name from Entrybook Column B.

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    3,877
    Shabbo
    This would show those ... or how?

    but Your sample result for
    A2 ... why not pqr ltd?
    (Entrybook has 10 times Vehno 4185 and two kind of Vendornames)
    A3 ... why not pqr ltd?
    (Entrybook has 5 times Vehno 4186 and two kind of Vendornames)

    Attached Files:

  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    You can test whether a vehicle is one of your fleet by using MATCH to return a record number or #N/A. ISNA tests for the latter and allows you to use INDEX/MATCH or VLOOKUP on the other table.

    = IF( ISNA(MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 ) ),
    INDEX( VehEntry[Vendorname], MATCH( [@[VehicleNo.]], VehEntry[Vehicle], 0 ) ),
    "ExpsFuel" )

    Attached Files:

    Shabbo and Thomas Kuriakose like this.
  4. Shabbo

    Shabbo Member

    Messages:
    206
    Dear Sir,
    It gives exact result What I wanted to get but can you please give me some explanation to understand this formula so I can try this formula for my other accounting workbook as well.
    Last edited by a moderator: Aug 13, 2018 at 5:25 PM
  5. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    Shabbo
    Firstly, the data is held as tables so structured references are used. A complete column is referenced by using the heading within square brackets, e.g.
    OwnVeh[Veh.No.]
    If the reference is to data held in a different table the expression is preceded by the table name. To reference a field in the current record an '@' is used to create a relative reference, so
    [@[VehicleNo.]]
    is the single value within the current record that is going to be looked up elsewhere.

    Turning to the workbook:
    Column1 (Spreadsheet column D) tries to find the vehicle within the Own vehicles table
    = MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 )
    Column2 wraps the result within ISNA() to identify fails
    = ISNA(MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 ) )
    Column3 shows a further MATCH used to find the vehicle in the Entrybook
    = MATCH( [@[VehicleNo.]], VehEntry[Vehicle], 0 )
    and contains the record number. That number is used in Column4 to return the vendor name. The final IF returns the vendor name where it is available and "ExpsFuel" otherwise.

    = IF( ISNA(MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 ) ),
    INDEX( VehEntry[Vendorname], MATCH( [@[VehicleNo.]], VehEntry[Vehicle], 0 ) ),
    "ExpsFuel" )

    I hope this helps.

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page