+ Reply to Thread
Results 1 to 6 of 6

what formula i can use for mt travel agency DB.

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    3

    what formula i can use for mt travel agency DB.

    hi all,

    please help me on below.

    I have data of travel agency. I want to know Company Name & Charges From Place 1 to Place 2 when I enter the names of both places.


    thanks
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: what formula i can use for mt travel agency DB.

    Hi,

    Welcome to excelforum.

    Can you please put what is your expected result?

    Thanks
    Ankur

  3. #3
    Registered User
    Join Date
    08-13-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: what formula i can use for mt travel agency DB.

    for example I want to go RESEARCH CENTER from CITY 4
    (manually DB search)
    minimum Charges are $ 325 offered by TA4
    max charges are $ 525 offered by TA3.

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: what formula i can use for mt travel agency DB.

    Hi,

    See the attached file. I have used the following array formula. These need to be confirmed by pressing CTRL+SHIFT+ENTER

    In C22:

    =MIN(INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),3):INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),7))

    In D22:

    =IFERROR(INDEX($A$1:$G41,1,SMALL(IF(INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),3):INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),7)=$C$22,COLUMN($C$1:$G$1)),COLUMN(A1))),"")

    drag this to the right


    In C25:

    =MAX(INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),3):INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),7))


    In D25:

    =IFERROR(INDEX($A$1:$G44,1,SMALL(IF(INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),3):INDEX($A$1:$G$17,MATCH(1,($A$1:$A$17=$A$22)*($B$1:$B$17=$B$22),0),7)=$C$25,COLUMN($C$1:$G$1)),COLUMN(A1))),"")
    drag this to the right.
    Attached Files Attached Files
    Last edited by cbatrody; 08-19-2015 at 03:52 AM.

  5. #5
    Registered User
    Join Date
    08-13-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: what formula i can use for mt travel agency DB.

    Thank you

  6. #6
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: what formula i can use for mt travel agency DB.

    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

    Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.

+ 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. Replies: 3
    Last Post: 04-18-2013, 04:17 PM
  2. Need help to keep records of my food product agency
    By dalipsinghbisht in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-30-2013, 04:49 PM
  3. formula to add/deduct an hour for travel [solved]
    By unclejambo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2013, 10:06 AM
  4. Hello: Newbie Excel Developer for a Home Healthcare Agency
    By jeffery_g in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-26-2012, 01:16 PM
  5. time formula with fixed travel time
    By loudguy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2009, 06:33 AM
  6. travel time distance formula
    By kapanenship in forum Excel General
    Replies: 2
    Last Post: 05-20-2008, 12:35 PM
  7. Project on Advertising Agency
    By Anzar in forum Excel General
    Replies: 4
    Last Post: 01-20-2008, 06:07 PM

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