Lookup based on more than two values

[rbrady]

Hello,

I have a situation where I would normally use a combination of the INDEX and MATCH functions to look-up a value in a cell range along the lines of

=INDEX(range, MATCH(some_value, column, 0), MATCH(another_value, row, 0))​

The first MATCH returns the row number of its match and second MATCH returns the column letter of its own match. But my problem now is that, for the first MATCH, I want to use two MATCH functions.

For example, if I have the data,

Animal  Date      Weight
Horse   1/1/2000  504.9
Horse	1/1/2001  511.9
Horse	1/1/2002  523.0
Horse	1/1/2003  556.1
Horse	1/1/2004  562.2
Horse	1/1/2005  570.0

, I can use a singe MATCH to find the weight of the horse on a certain date because all of the entries are for the horse. But if I have more than one kind of animal:

Animal  Date      Weight
Fish    1/1/2000  1.1
Hippo   1/1/2000  1356.2
Horse   1/1/2000  504.9
Monkey  1/1/2000  23.7
Fish    1/1/2001  1.4
Hippo   1/1/2001  1401.1
Horse   1/1/2001  511.9
Monkey  1/1/2001  23.4
Fish    1/1/2002  1.3
Hippo   1/1/2002  1396.5
Horse   1/1/2002  523.0
Monkey  1/1/2002  24.8
Fish    1/1/2003  1.6
Hippo   1/1/2003  1389.7
Horse   1/1/2003  556.1
Monkey  1/1/2003  24.2
Fish    1/1/2004  2.1
Hippo   1/1/2004  1392.9
Horse   1/1/2004  562.2
Monkey  1/1/2004  24.2
Fish    1/1/2005  1.7
Hippo   1/1/2005  1407.6
Horse   1/1/2005  570.0
Monkey  1/1/2005  24.6

, then I need to specify a date and an animal. Is there a way to do this?

If possible, I would like to be able to keep the data in the columns separate (that is, not create a unique value by concatenating Animal and Date) and be able to separate the columns from being next to each other, too (like, have the animals in column A and the dates in column F).

To put it a different way, is it possible to use the INDEX and MATCH combination from the top, but find matches (to different values) in two or more columns first, and then find the match in the row? (I have attached a workbook with the example I used above.)

Thank you very much!

 

[shades]

Howdy. I would probably add another column and do a concatenation of the two columns for lookup, then use that in the INDEX/MATCH formula. You can always hide that helper column. This is easier to troubleshoot as well.
________
Vista

 

[ptm0412]

Use Function SumProduct in speccial way

Take a look at my attachment, in it I use the function SumProduct to calculate. This way is useful if you want to lookup with even 3 conditions or more.

 

[rbrady]

Thank you for your replies and sorry for the delayed response. ptm0412: Thank you very much for your formula; it looks like this could be a big help, but I am struggling to understand it—can you please explain it to me? If the formula is,

=SUMPRODUCT((A2:A25="Horse")*(B2:B25=DATE(2002,1,1))*C2:C25)​

, I am guessing that it multiplies 0×0×value_to_return for each row until it hits the row with the required parameters, 1×1×value_to_return, and then sums all of the zeros plus the one row that didn’t equal zero, but I am not totally sure. If that is how it works, do you know why,

=SUMPRODUCT((A2:A25="Horse"),(B2:B25=DATE(2002,1,1)),C2:C25)​

, which returns 0, doesn’t work? (This one makes more sense to me in my mind, but since it doesn’t work, I am very confused.)

Similarly, do you know why an entire column, or at least a range with the heading (e.g., A1:A25), can’t be used for a range? The SUMPRODUCT help file says that the function treats non-numeric entries as zeros, so I would expect it to work OK, but if I include the first row in all of the ranges, the formula returns the #VALUE! error.

Also (sorry!), can you please confirm that something like,

(A2:A25="Horse")​

, means to return a 1/TRUE where a cell in the range equals “Horse” but 0/FALSE where it does not? (I have not seen this syntax before, and it looks useful!)


PS: Please ignore the extra spaces in the DATEs in the formulas because I could not get it to stop from showing-up.

 

[Brianwarnock]

http://www.access-programmers.co.uk/forums/showthread.php?t=145593&highlight=sumproduct

The above thread contains info on sumproduct.
Post 17 contains a zip file of a document which came from the first site listed in post 18. It makes for educational reading if you are interested in learning about Sumproduct.

Brian

 

[ptm0412]

Dear Rbrady,
It is really what you explain the way Sumproduct works.
Whenever you multiply a logical value by a numeric cell's value, Excel considers logical value as 0 and 1.
But if you use Sumproduct as it origin structure, Excel considers them as True/False and it guide to wrong result.
You can see it when you click the fx icon to view the construction. It is a little difficult for me to understand all, but I have learned how to use it in http://www.giaiphapexcel.com/forum/cmps_index.php

Anyway, Sumproduct is one of Array Function so each array must be identified as the same type of data: numeric, text, date ...
It explain why it can nót work if you include the column title "Weight" into the nummeric range.
Hope that helps,

 

[rbrady]

Thank you for your links and explanations—I think I understand this use of SUMPRODUCT now. To answer my own questions for the record,

If the formula is,

=SUMPRODUCT((A2:A25="Horse")*(B2:B25=DATE(2002,1,1))*C2:C25)​

, I am guessing that it multiplies 0×0×value_to_return for each row until it hits the row with the required parameters, 1×1×value_to_return, and then sums all of the zeros plus the one row that didn’t equal zero, but I am not totally sure. If that is how it works, do you know why,

=SUMPRODUCT((A2:A25="Horse"),(B2:B25=DATE(2002,1,1)),C2:C25)​

, which returns 0, doesn’t work?

Can you please confirm that something like,

(A2:A25="Horse")​

, means to return a 1/TRUE where a cell in the range equals “Horse” but 0/FALSE where it does not? (I have not seen this syntax before, and it looks useful!)

The first SUMPRODUCT works like it does because, yes, the range=value pairs are comparisons, but really return a TRUE or FALSE, not a 0 or 1. Multiplication (*) is used to implicitly convert them to numeric values so the formula does not err.

The second SUMPRODUCT returns zero because the string comparisons are not converted to a number. So even if a cell does contain “Horse”, then the returned value for that comparison is TRUE. When the SUMPRODUCT function as a whole considers this, it sees that TRUE is not a number, so it treats it as zero, and this goes on with each comparison and there is never a non-zero product to sum.

Similarly, do you know why an entire column, or at least a range with the heading (e.g., A1:A25), can’t be used for a range? The SUMPRODUCT help file says that the function treats non-numeric entries as zeros, so I would expect it to work OK, but if I include the first row in all of the ranges, the formula returns the #VALUE! error.

Anyway, Sumproduct is one of Array Function so each array must be identified as the same type of data: numeric, text, date ...
It explain why it can not work if you include the column title "Weight" into the nummeric range.
Hope that helps,

I found that I like using the double unary operator, -- (it was a big “duh!” to me when I realized that it is the same as two negative signs), which converts a non-numeric data to a numeric type:

=SUMPRODUCT(--(A1:A25="Horse"),--(B1:B25=N(DATE(2002,1,1))),(C1:C25))​

Column A is all text, so the heading is treated the same as the text data. For Column B, the heading is text, but the data is date, so the dates are converted to numbers first (the -- could be used instead of N), and then the -- can work to make sure both of them are numbers. I would expect --, or really N because it is very explicit, to work in for column C, but it doesn’t (I’m not sure why).

Brianwarnock’s link [second Note at the end of the section] confirms that whole columns cannot be used.

 

[ptm0412]

It will be easy to understand, if we consider (as maths) that single minus (-) means to multiply a number by -1, and double minus (--) means multiply that number by (-1) x (-1) then equivalent to multiply by +1. And we remember that a logical value, if multiply by a numeric will be treated as number (1/0).

Sorry, if the link I have introduce is most posted in Vietnammese, but it is useful with me. Anyway, anyone could ask questions there, or share your own experience there, in English also.
Regards,

 

[rbrady]

Ah, I understand now why -- works for Columns A & B, which is because, by using the comparison (=), the returned value is a boolean, which is easy turned into a number by --.

This makes me realize that it makes sense to get an error when using the double unary on Column C (when including the heading) because the heading string is not a logic value, just a string, which has no numerical analog. If no -- is used, it makes sense that it works because the SUMPRODUCT documentation says, “SUMPRODUCT treats array entries that are not numeric as if they were zeros.”

Not that it really matters, but unfortunately, I still don’t understand why the use of the N function (e.g., N(C1:C25)) yields an error. N should turn the heading into a 0 and not change any of the other data because it is numerical. On the SUMPRODUCT level, the heading string-now-number should match well with the other columns because they are numbers, converted by the double unary (where N would work just as well in its place). I must be missing something that is probably pretty simple, but I’m mostly thinking out loud now.

Thank you everybody for all the help you have given on this!

 

[Brianwarnock]

I'm no expert but I think it goes like this.
N like the double unary is used to coerce conditional arrays from TRUE/False to numbers 0 and 1, but the array C1:C25 is not a conditional array and the use of the N function causes it to return one number i.e. 0 and thus you get a #VALUE error as this "array" is now not the same dimensions as the others.

Brian

 

[rbrady]

Ahh, I see. Thanks!