CODEWITHSUNDEEP
excelvlookup
SamH123
SamH123

Reputation: 159

How to lookup a value in Excel using 2 conditions where each condition also has 2 properties

I have a list of data like this:

x      y
87.5   11
61     3.5
22     43

and so on.

I want to return the value from column 'value' for each pair of coordinates from a table that looks like the following:

x_min  x_max  y_min   y_max   value
  70     80     0       5      0.054
  70     80     5       10     0.187
  80     85     0       10     0
  85     100    10      20     0.765

It's basically a sports pitch split up into zones, and when I have a specific coordinate I want to return the value that defines the zone the coordinate is in.

I have spent quite a lot of time trying to figure it out but have not managed. I am not sure if sumproduct can be used to multiply the columns in this example, sumproduct(and(a3>d1:d4,a3<=e1:e4)*and(b3>f1:f4,b3<=g1:g4)*(h1:h4)) doesn't work

I tried similar with index & match that I saw in other examples but it seemed I had to use the and function with a pair of inequalities again which I don't think works?

I also tried using a helper column which I could get working eventually but isn't a very elegant solution and may be impossible for more complicated things I do in the future.

Thanks for any advice on how best to do this

Upvotes: 1

Views: 82

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You were close with your SUMPRODUCT 

=sumproduct((a3>dd:d4) * (a3<=e1:e4)*(b3>f1:f4)*(b3<=g1:g4)*(h1:h4))

Upvotes: 2

Related Questions