 # 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 !!!

## Files

• Re: Multiple criteria lookup

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 VBA-if 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

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

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 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,