• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Multiple Criteria Lookup

M

mistryman

New Member
Hello Friends of Chandoo

First off I hope I named the thread right. If I have made errors please do let me know
and I will ensure it will not happen again the next time I post.

I am long time follower first time posting on the forum. Normally I solve my problem
through sifting through the forums but I cannot seem to find the answer.

I have workbook with sheets for every day of the month, labeled 1-30.
Each sheet is exactly the same format. Columns A through C on each sheet are populated
at the beginning of the month and contain projected transactions for the month.
Column A is the transaction number and Column B indicates the type of transaction
whether it is pay or receive. Column C is the amount. In each sheet column D is populated
each day with a confirmation code confirming whether we paid or received the amount.

On a separate summary sheet I am trying to create a dynamic column that will look up
the reference number in column D based on the Column A ,B and the date. When I change the date, Column A an Columns C on the summary sheet it will look to the appropriate sheet and return the reference number if available. This is all contingent on the fact that I know what values
for Column A and B that I am looking for( which I do). When Column A and B are concatenated
they produce a unique value. In column A a transaction will always be posted twice.

So I tried an index match formula which I was successful with but I was not able to figure out
how to make it dynamic based on the date cell in my summary sheet.

Any help would be awesome.

Thanks so much.
 
Since you've already got the INDEX/MATCH portion, I'll just tackle the dynamic sheet problem. For this, you'll want to use the INDIRECT function. INDIRECT lets you build string that can be interpreted as a cell reference. So, if the cell with Date (aka, sheet name) is in B2, you could use this:
=INDIRECT("'"&B2&"'!A2:A100")
to give you the range A2:A100 on that particular sheet. Does that help you out?
 
Hi Luke Indirect is exactly what I was looking

I was sucessful in using the indirect in the index portion of the formula however it does not seem to be working for last part of the match function.

=INDEX(INDIRECT("'"&$Q$5&"'!"& $R$5),MATCH('Sheet1'!A12&"#"&'Sheet1'!K12,INDIRECT("'"&$Q$5&"'!"& $R$4)&"#"&INDIRECT("'"&$Q$5&"'!"& $R$6),0))

Thanks in advance.
 
Since you are concatenating the MATCH arrays, you need to make sure you confirm the formula with Ctrl+Shift+Enter.
Other than that, the formula appears to works. assuming:
Name of sheet in Q5
Some range address like "A:A" in R4:R6
 
you were correct. Alt Shift Enter did the trick!

Thank you very much Luke you of were great assistance.

Have a wonderful remainder of the week.
 
Hey Luke

A quick followup to this. I have working with this formula and I am finding the indirect function to be taking quiet a long time. Do you have any suggestions as to what I may be able to use that is less volatile and will speed up the processing time?
 
Maybe... Since the sheet that we are looking for keeps changing depending on variable/input, we need someway to make this dynamic. I'm assuming that right now you have the INDIRECT function in several cells. Each of those is a volatile function, which, as you surmised, is costing you calculation speed. We might be able to boost the time a little bit by creating a NamedRange that does the INDIRECT function just once, and then have your other formulas reference that range. This should, in theory, reduce the number of volatile functions.
 
You must log in or register to reply here.
Back
Top