Multiple criteria lookup

    forum.ozgrid.com/index.php?attachment/33829/Yes I'm trying to use Vlookup -to find spacific critiria after looking two differant range
    I mean Vlook up "Date " in cell and Agent Name" in another cell and return "after matching the criteria in both cells "Date & Agent " to return the results
    See the Example attached
    For Example
    Date in c1 "1/07/2010"
    Agent Name " in E1 " Thomson"

    Vlookup(date , Agent_Name( if it matching find the Result from the Table "Table where the Date and Agent Name No.# of room sold,and so on....
    the point is Date from 1-30 or 31 for Agent Thomson
    and the date from 1-30 or 31 for Agent TUI
    and so on so forth
    how we can force Vlookup "to find the result after referring to Date&Agnet Name- see attached
    N.B
    It can work with Vlook- or Match- Indirect-index?!!!
    Just in case there is no answwer in which book I can read to find the answer !!!

    Re: Multiple criteria lookup


    Headings:
    N1: Agent Name
    O1: Date
    P1: Table Row
    Q1: TTl Rooms
    R1: Daily Bookings

    N2: <enter agent name to look up>
    O2: <enter date to look up>
    P2: =SUMPRODUCT(($H$2:$H$36=$N2)*($I$2:$I$36=$O2)*ROW($K$2:$K$36))
    Q2: =INDEX($K$1:$K$36,$P2)
    R2: =INDEX($L$1:$L$36,$P2)

    This will only work if there is a single match only. If there might be multiple matches then a more complex formula is needed for P2.

    Re: Multiple criteria lookup


    Ayman,

    Please do not quote previous posts unless it is absolutely necessary to make a response clear. Then only quote the minimum amount needed. Thank you.

    You need to add one Named constant:
    QuiteBig =10000000 (10,000,000)

    P2: =SUMPRODUCT(SMALL(($H$2:$H$36=$N2)*($I$2:$I$36=$O2)*ROW($K$2:$K$36)+(((($H$2:$H$36=$N2)*($I$2:$I$36=$O2))=0)*QuiteBig),ROW()-ROW($P$1)))

    This formula can be Autofilled down for each multiple result you want returned.

    Re: Multiple criteria lookup


    How doi u thing we need only to look at ONLY 2 Varient "Date&Agent" - it make it easy "
    after look at to return the result---- I tried it beofre I post the first Post for me here --- Actual I asked you coz I know u better than me -- For sure yes ... well Let 's try a code in VBA !!!

    Re: Multiple criteria lookup


    I did it already but it return No.# 1000000!-- I'm sure I did all the steps I made -- but its did not work !!

    Re: Multiple criteria lookup


    I'll make it easy for all -- do u have a book Excel VBA I can read then I can find out myself--just helping each othere
    Many thanks for al;l yr effort u did ---- I want to share all of u after reading --and If some one find out how we can do it - more than welcome

    Re: Multiple criteria lookup


    One more silly question
    In the Sheet attached to this post u made agreat "Indeed" a great formula
    in case I want this formula " to be in sheet1" and the Data in sheet2"
    ROW() should indecate to the same date in the same row in the same sheet "the question is how we can set formaul in sheet1 : where the result exist" to be referred to another sheet where the date exist?

    the formula
    SUMPRODUCT(SMALL(($H$2:$H$36=$N2)*($I$2:$I$36=$O2)*ROW($K$2:$K$36)+(((($H$2:$H$36=$N2)*($I$2:$I$36=$O2))=0)*QuiteBig),ROW()-ROW($P$1))) ---------> ROW () here supposed to be in the same sheet- what if I want to have this formnula in one sheet and the result in the ther sheet

    one more thng how we can make this formaul " as Function using VBA"
    another lst quetion
    Quitbig "=100000" referr to what ? to cell- range in where it can be set
    Many thanks
    waiting yr usula co-operation

    Re: Multiple criteria lookup


    QuiteBig is just a large number that is more than the possible number of rows used by Excel. It is used as part of the SUMPRODUCT to make sure that SMALL does not return 0 instead of the smallest ROW found.

    ROW() and ROW($P$1) are only used as indexes to calculate the k parameter for SMALL. It does not matter what sheet they are on providing that $P$1 is the correct row relative to the data.

    If your Formula is on a different sheet from your data then you will need to change the references to the ranges for the data (eg Sheet2!$H$2:$H$36).


    If performing this function in VBA it would be a totally different approach using AutoFilter. That would be something for a separate thread, not to be carried on in this one.

    Re: Multiple criteria lookup


    again and again many thanks for ye fast reply
    well it means the P1 "ROW()" it does not influances where the data exist - in other hand --- if the ROW() in defferant sheet it supposed to retrun the result whereever it exist --- but when I moved the formula to another sheet the following unexpected reult appeared "NAME?" - and when I tried to moify the same formula "Value!" apperared it means still somthing wrong I;m doing
    Sorry it seems that I'm Dump!!!

    Re: Multiple criteria lookup


    As I said above ROW() and ROW($P$1) are not affected by which sheet. They are only returning integer numbers:

    ROW() returns the row number for the row the actual formula is in
    ROW($P$1) is effectively a constant for the top row (header) of the table.

    If copying the formula to another sheet caused #NAME then it is likely that when you defined QuiteBig it was defined as a local name (only exists for the sheet it was defined on) you need to change the QuiteBig definition to be Global. I don't use 2007 so you will need to look that up elsewhere as I cannot give instructions on how to change that.

    The formula is quite complex and if you make any errors in changing it then it will simply fail. a #VALUE error is most likely caused by a difference between the number of cells in each range. For SUMPRODUCT to work then the ranges must all be the same size.

    Re: Multiple criteria lookup


    " One More question"
    If I need the same attached Xs to be VBa " as UFD" -- To make it as Function using VBA"
    Auto Filer"
    Can U help
    - I only can Recod and its not right

    Re: Multiple criteria lookup


    alfa-alfa,

    While we welcome you to Ozgrid please do not post new questions in other threads. Ozgrid rules allow one question (or close follow ups) by the original poster only. Please start your own thread and if necessary to explain your issue then link back to a previous thread.

    Also, in future please do not requote previous posts unless it is absolutely necessary to explain yourself.

    Thank you.

Participate now!

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

Register Yourself Login