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 130 or 31 for Agent Thomson
and the date from 130 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 Indirectindex?!!!
Just in case there is no answwer in which book I can read to find the answer !!!
Multiple criteria lookup
 aymansalem
 Closed



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
Yes and this complex formula I'm looking for  using VBAif its like this it will be super

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
Many thanks  but "Name?" is the only what I got!!!!


Re: Multiple criteria lookup
It dose say you would have to define a Named constant...
Formulas Menu > Named Cells Panel > Name a Range
In the New Name dialog:
Name: QuiteBig
Refers to: 10000000
Then OK
Should all be fine then. 
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 myselfjust 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
I have added some duplicate data to the bottom of your original data table. Have a look at how it is done. I don't have any books on Excel. I work from the help files and the web.


Re: Multiple criteria lookup
Super what u did but If I want to drag the Formula to the next row  It did not work

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 cooperation 
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
alfaalfa,
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!