+ Reply to Thread
Results 1 to 5 of 5

Creating a formula that finds single values dependent on multi-variable relations

  1. #1
    Registered User
    Join Date
    10-02-2016
    Location
    Waterloo, Ontario
    MS-Off Ver
    2013
    Posts
    5

    Question Creating a formula that finds single values dependent on multi-variable relations

    I have been having trouble creating a formula that will do what I need. I know what I want it to read but I can't write it out to make any sense.

    I need for E9 to look up the price of flights for the airline listed in cell C9 using the table in the worksheet FlightPrices. The formula should account for the amount of days Elizabeth wishes to stay in that country. If the price is unavailable, the cell should display a value of 0. It should be in a form that I'll be able to fill the formula down to cell E21.

    Any insight would be greatly appreciated! Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Creating a formula that finds single values dependent on multi-variable relations

    Hi Bondras,

    Try this formula in E9 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Registered User
    Join Date
    10-02-2016
    Location
    Waterloo, Ontario
    MS-Off Ver
    2013
    Posts
    5

    Re: Creating a formula that finds single values dependent on multi-variable relations

    Quote Originally Posted by FlameRetired View Post
    Hi Bondras,

    Try this formula in E9 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hi Dave,

    I tried your formula but it is returning a value of "-" for every cell. This is odd as it should at least be returning a value of "0" if I'm understanding correctly.
    Thanks again!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Creating a formula that finds single values dependent on multi-variable relations

    Alternative ..

    =IFERROR(INDEX(FlightPrices!$C$4:$D$15,MATCH(Countries!$C9,FlightPrices!$B$4:$B$15,0),MATCH(Countries!$D9 & " days",FlightPrices!$C$3:$D$3,0)),0)

    You will get "-" if the result is zero because of the formatting you have used.

    _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)

    If you want 0, you colud simple format as "Currency" with "$" symbol

  5. #5
    Registered User
    Join Date
    10-02-2016
    Location
    Waterloo, Ontario
    MS-Off Ver
    2013
    Posts
    5

    Re: Creating a formula that finds single values dependent on multi-variable relations

    Thanks John,

    That solved the problem and all additional costs were able to be retrieved as well. Perfect!

+ 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. [SOLVED] Running if statement through VBA on single cell values dependent on selection
    By gottnoskill in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2014, 01:13 AM
  2. [SOLVED] Creating an IF formula with SUM using set values and variable values
    By rjordine in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-26-2013, 01:05 AM
  3. Need help with formula with dependent on multi criteria
    By kuanyaw in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 12:59 AM
  4. Replies: 4
    Last Post: 03-12-2013, 06:37 AM
  5. Creating an add-in: References and Relations
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2012, 04:50 PM
  6. Replies: 3
    Last Post: 02-11-2005, 07:06 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