Find a result based on three criteria

[marketal]

Hi Guys,

I'm a forester involved in processing data collected in the forest.

I have a dataset of 4 fields and 12000 rows.

I need to find the value in the 4th field - it is based on the combination of the first three fields. I thought it would be simple (it is in MS Access) but it's not in Excel.

Any help would be gratefully received. I've created about 40 rows from it in the attached example.

Cheers

Mark

[XLent]

given version, XL2010, you might try:

Please Login or Register  to view this content.

above would return 6, 4, 6 & 8

[marketal]

given version, XL2010, you might try:

Please Login or Register  to view this content.

above would return 6, 4, 6 & 8

Brilliant! I'm familiar with LOOKUP, but everything I read suggested a combination of MATCH and INDEX, which I couldn't get to work - principally because I couldn't understand it!

Thank you very much indeed!

[marketal]

XLent,

This is really good, and it seems to be working perfectly in my spreadsheet, but I don't understand it. It seems to performing a calculation; what is it looking up in the =LOOKUP(2,1/…?

Can you quickly describe what it's doing, so if I come across a similar problem in the future, I can resolve it myself.

Cheers

Mark

[XLent]

Hi Mark,

In a nutshell... 3 key principles to consider with LOOKUP(lookup_value, lookup_vector [,result_vector])

1. LOOKUP assumes all values in the lookup_vector are in ascending order (irrespective of reality)
2. LOOKUP will ignore values in the lookup_vector that are not of the same data type as the lookup_value and, crucially, this also includes error types
3. LOOKUP retrieves the last value <= lookup_value from lookup_vector or, where specified, the associated value from the otherwise optional result_vector

So, in this case the lookup_vector can only be comprised of either 1s or #DIV/0!

lookup_vector value will be 1 where all Boolean test satisfied - i.e. 1/(TRUE*TRUE*TRUE) --> 1/1 --> 1
lookup_vector value will be #DIV/0! if one or more of the Boolean tests are not satisfied - e.g. 1/(TRUE*FALSE*TRUE) --> 1/0 --> #DIV/0!

We set lookup_value to be 2 as we know this will be bigger than any possible value within the lookup_vector (1 would be the max)

Then, in line with the 3 key principles of the LOOKUP function, we know LOOKUP will return the value from the result_vector that is associated with the last instance of 1 in the lookup vector

[marketal]

Xlent,

Thanks very much. I guess I meant I was au fait with VLOOKUP and HLOOKUP. This is a somewhat different ballgame.

Thanks for taking the time to explain it so simply - I appreciate it very much!

Cheers

Mark