Search tabulated data and return value that meets multiple criteria

[prestopr]

The table has 11 columns and ~1000 rows of data.


the returned value is the intersection of the following criteria;


column a value = apples

column b value = green

row header value = crate


I have definitely over used sumproduct which slows the calculating process and would prefer to solve using something different such as;

hlookup

vlookup

index

match

 

[Hui]

Prestopr


Firstly, Welcome to the Chandoo.org forums


If you are only returning a single or a few values from the table described above, Sumproduct won't have any noticeable effect on speed


If you are using Sumproduct to populate the table described then yes, it will have an impact on speed


Can you me explicit in what you are doing, extracting values from the table or populating the table from another source?


If you are populating the table maybe look at using Index/Match