2

I have the lookup table below, where columns C through E are different places:

lookup table Below, I am trying to lookup the value based on the columnar and row criteria:

lookup criteria

How can I find the value Orange, 2019, and Plants in cell D2?

I have tried various index match, vlookup and hlookup techniques, but can't seem to figure this out.

  • Do you want to count how many rows of the first spreadsheet satisfy the criterion? – Rodolfo Oviedo Nov 8 at 13:16
2

If the values to be returned are numeric and the combination of color, year and place is unique, then you can use a SumProduct

=SUMPRODUCT($C$2:$E$10*($A$2:$A$10=I2)*($B$2:$B$10=J2)*($C$1:$E$1=K2))

enter image description here

For text data, this can also be done with Index/Match but that will be more complex.

  • 1
    That's actually pretty slick. I didn't even consider SUMPRODUCT of the matrix. I did the Index/Match method. high-five This would have the added benefit of summing rows of duplicate matches. – TheCompWiz Nov 8 at 17:12
0

Assuming your sheet with all of the data is named "Sheet1", you can use this formula in the "Count" field to get the appropriate value.

=INDIRECT("Sheet1!" &SUBSTITUTE(ADDRESS(1,MATCH(C2,Sheet1!$C$1:$E$1,0)+2,4),"1","") &MATCH(1,(Sheet1!$A:$A=A2)*(Sheet1!$B:$B=B2),0))

After adding the formula, you MUST press <control>+<shift>+<enter> to get it to calculate the value. (It sounds strange... I know. trust me.)

Ok... so here is the breakdown: First, we need to find the column we want to return data from (trees/plants/etc...) This is in the format of "Sheet1!C" for trees, or "Sheet1!D" for plants, etc... This bit of code does exactly that:

"Sheet1!" &SUBSTITUTE(ADDRESS(1,MATCH(C2,Sheet1!$C$1:$E$1,0)+2,4),"1","")

Essentially, we use MATCH to find where the matching header is. Our Match-statement looks at the cell "C2" in this case, and looks in the headers of C1-E1. (we use the $ to make sure that those cells don't change if we do a fill-down) For this case, it will return 2... meaning the 2nd column of the ones we are matching against. We need to add 2 more because we skipped "Color" and "Year". So, what we really looking at is the 4th column. We can use the "Address" function to return the column name. (It actually returns "D1", but we use the substitute command to remove the "1")... so we get "D".

Now that we have "Sheet1!D", we need the row. The second part of the line is as follows:

MATCH(1,(Sheet1!$A:$A=A2)*(Sheet1!$B:$B=B2),0)

Once again, we're using good ol' MATCH. This time, we're turning the row-data into a truth-table. Referring to "Sheet1!A:A", returns an array of all data in column A, but adding a comparator returns 1 if the comparison is true, and a 0 if it is false. So, in this case we end up with an array where all "Orange" rows are a 1, and all not-Orange are 0.

BUT WAIT! There's More! Notice how we multiply by a 2nd array doing the same thing with column "B"? Excel is smart enough to do matrix-math for us. The array returned from the "B" column is only 1 for values that are "2019" in this case. Which is useful, because when we multiply the array where they're all Orange, and the array where they're all 2019, we end up with an array where they MUST BOTH be Orange and 2019. We simply MATCH for a row where "1" is present, and we end up with the matching row. "2" in this case.

So, how that we have the row, we simply append it to the previous string, and end up with "Sheet1!D2", and pass that to INDIRECT to obtain the value in that field. A lovely "2". Which is what we wanted all along.

Since I did include the "$" marks on fields that should not change, you should be able to use "Fill-Down" methods to dynamically adjust the cells relevant to the position, while keeping the static search criteria static.

Now... why must we press <control>+<shift>+<enter> to have it generate the proper answer? Well, Excel has this strange thing where if you are doing matrix-math, you must use CSE to get it to do the proper math. If you edit the function later, keep this in mind. CSE CSE CSE... it will save you some headaches.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.