Multiple Criteria Matches

    To whom it may concern,


    I am new to excel, and have stumbled across your wonderful site.


    My query, if anyone can help is the following:


    I am trying to set up a worksheet whereby two critera when matched from a drop down list will populate cell 'x' with a coressponding answer.


    i.e.


    if you have Destinations then a From and To column with various locations listed below, then in a thrid column you have an output cell which is kilometres


    What formula can i use to match the various location 'to' and 'from''s with the correct kilometre match?


    Any assistance would be MOST helpful,


    Thanks in advance,



    Ben

    Re: Multiple Criteria Matches:


    Where is your source data held. If you create a table with all the combinations of from and to locations, using "&" function to create a unique record, with the associated distance, then assuming From location is in A1, and To location is B1, this should work;



    =VLOOKUP(A1&B1,SourceData,2,0)



    where SourceData is a table holding your table of distances.


    Any help?


    Richard

    If absence makes the heart grow fonder, and familiarity breeds contempt, perhaps my wife should live in Darwin?

    Re: Multiple Criteria Matches:


    Hello All,


    Thanks everyone for your speedy replies - Richard i will play around with this, however i really am quite new to excel, so not even sure if what you suggested will make much sense to me.


    I have posted a REALLY basic outline of the sort of thing i'm thinking of.


    Not sure if this puts more clarity on what i'm trying to achieve.


    But basically i want it to auto complete the kilometres field when two locations are selected. Not sure how to link these as you were suggesting a'source file'?


    Let me know -


    Thanks,



    Ben

    Re: Multiple Criteria Matches:


    Hi Ben,


    Quote

    basic outline of the sort of thing i'm thinking of



    This may require some more explaining, but here's a start for you.


    It still has your dropdown lists but it now has a formula on Sheet1 to return an answer and so much more! <g>.


    Named ranges on Sheet2:


    These two named ranges are for your dropdown lists:


    L8:L13 = "From"
    M8:M13 = "To"


    These are required by the "answer" formula on Sheet1:


    D8:D13 = "FromList"
    E7:J7 = "ToList"
    E8:J13 = "Distance" This is a distance matrix- like on a map!


    The "Distance" table is populated by formulas for distance calulation. The formulas work off of Range B1:B11. Change a distance and the table will update.


    Formula on Sheet1 looks in named range "Distance" for "From" then "To" and returns result, result being the Intersection of the two, of course, just like a person looking at a distance matrix on a map key.


    In other words the formula looks down the "FROM Column then across the "TO" column and returns the intersection answer.


    Am I close?

    Re: Multiple Criteria Matches:


    Dear Rbrhodes,


    Thanks for your assitance - I have attached what i have been working on. Not sure if there is a better way to do this, as can see it getting messy with lots of combinations entered in.


    Is there any way for the spread sheet to just show blanks / zeros when nothing is selected or a non matched combination? I.e. for the $$/Klms heading?


    What do you think?


    Cheers,


    Ben

    Re: Multiple Criteria Matches:


    Hi Ben,


    The Vlookup and the divide formulas will both return a error if they can't do what they are supposed to. This is good. You test for the error with an 'IF' statement and if it is indeed an errror the IF returns "" (blank) or 0 if you want.


    Your Product column, Row 11 as an example:


    =IF(ISERROR(VLOOKUP(E11,'Master File'!D$4:G$31,4,0)),"",VLOOKUP(E11,'Master File'!D$4:G$31,4,0))


    =If (if the lookup is an error return, "" ,else do the lookup.


    Here's the rest just so we're clear:


    KM:
    =IF(ISERROR(VLOOKUP(E11,'Master File'!D$4:E$20,2,0)),"",VLOOKUP(E11,'Master File'!D$4:E$20,2,0))


    RATE:
    =IF(ISERROR(VLOOKUP(E11,'Master File'!D$4:F$26,3,0)),"",VLOOKUP(E11,'Master File'!D$4:F$26,3,0))


    TOTAL:
    =IF(ISERROR(K11*L11),"",K11*L11)


    $/KM:
    =IF(ISERROR(M11/J11),"",M11/J11)


    As to the multiple entries, I don't see that you have the makings of a matrix here. All of your From/To destinations are different, not continuos as in my example.

    Re: Multiple Criteria Matches:


    Hi Again,


    Thanks for your assistance, so quick!!!!! This site is brilliant.


    I have started to enter in the IF statements, and this seems to be working, so for the time being the spreadsheet is a goer!


    Thanks to everyone for sharing the expertise.


    Hopefully will not have to bug you for a while!


    Cheers,



    Ben

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login