• 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.

Error Attempting Multivariable Lookup

MSC Bobs

Member
Hello everyone,

I am attempting to perform a multivariable lookup, but I am getting an error and I'm hoping someone can help me identify what is causing the error and help me fix it.

I was building a dashboard and performed the lookup in question without any errors and so I don't know why I'm getting an error now.

My workpapers were becoming messy and unorganized and so I thought I would start with a clean workbook. It worked just fine in my other workbook.

For that matter, I have not been able to get SUMPRODUCT to work either.

I would appreciate if anyone is able to help me. Thank you.
 

Attachments

  • Multivariable Lookup Error.xlsx
    268.2 KB · Views: 7
Try below

INDEX(tbl.Schedule[Opponent],MATCH(1,(tbl.Schedule[Date]=$A7)*(tbl.Schedule[Market]=$R$1),0))

Acknowledge with CTRL + SHIFT + ENTER

There was a problem in lookup_array in the MATCH function used
 
Thanks, Asheesh.

Is there a way to perform the lookup over the entire array, or do I need to stay with a single-cell lookup?

In the other workbook I mentioned, I isolated all of the records relating to one value and deleted the rest to keep the workbook small. The array lookup worked fine.

Once I got it all figured out, then I replaced the table relating to the single variable with the full data table and all the variables, which led to my problems.
 
Confused :(

What are you referring to when you say "Is there a way to perform the lookup over the entire array, or do I need to stay with a single-cell lookup"

Can you elaborate please
 
My preference is to perform these calculations over an entire array (to prevent tampering and accidentally deleting), but sometimes it seems like the only way I can get the function to work correctly is by doing a single-cell array and then dragging that formula down.

For whatever reason, the MATCH function seems to be particularly sensitive. Often I can get it to work for a single cell, but then if I use the exact same syntax and replace cell references with arrays it stops working.
 
Thanks, Narayan. That is exactly what I was trying to do.

I felt like it should not have been difficult, but for some reason I could not get it to work and I must have tried every conceivable method for multivariable look ups.

In that vein, could you tell me why the SUMPRODUCT is returning a #VALUE error? SUMPRODUCT is my preferred method for a multivariable look up. I thought I used to be pretty good at using that function, but lately it has been returning an error and has me stumped.

The formula I was using is:
=SUMPRODUCT((tbl.Schedule[Date]=TEXT($A7,"m/d/yyyy"))*(tbl.Schedule[Market]=$R$1)*(tbl.Schedule[Opponent]))
 
Back
Top