How to find value of a cell based on multiple criteria to look into multiple rows and cols

[karkibin]

I am having issues with finding a specific value based on multiple criteria and the criteria needs to be searched into multiple rows and columns. I have tried using match, index, if, row functions but nothing worked. I have included an attachment with an example and also posed the question. Hope that helps. If you look at the example, I am basically trying to find the value of the goods that matches the company name, and also matches the states in row 1 and Col B. I know how to use the match function to find the row number and col number for the criteria but i have multiple cols that needs to be looked into for the criteria and that is where I am failing. Please help.

[drbacon]

I opened the file, but I am not sure I understand the values on the table. For what I can see, Row 4 has the values that were shipped from CA, and Column E has the values that were shipped to CA? So that Cell E4, shows the values shipped from CA to CA? Is this correct?

[karkibin]

Thank you so much. The former one worked perfectly.

[Melvosh]

If you're looking for only the value for a specific company shipped to and from CA, try this array formula, entered with ctrl+shift+enter:

=INDEX($C$2:$G$11,MATCH(1,($A$2:$A$11=$A2)*($B$2:$B$11="CA"),0),MATCH("CA",$C$1:$G$1,0))

This will retrieve a single value at the intersection of the CA row and CA column, for Test Company 1.

If you're looking to sum up every value that shipped from CA plus every value that was shipped to CA, try this formula:

=SUMPRODUCT(($A$2:$A$11=$A2)*($B$2:$B$11="CA")*$C$2:$G$11)+SUMPRODUCT(($A$2:$A$11=$A2)*($C$1:$G$1="CA")*($B$2:$B$11<>"CA")*$C$2:$G$11)

This will add up all the values in the CA row for Test Company 1, plus all the values in the CA column for Test Company 1. The second formula will not include the cell where the CA row and CA column intersect.

Does either of those give you the result you're looking for?

[karkibin]

Yes you are correct. Also, the formula needs to match the company names as well. For e.g. I am looking at Test Company 1 in this example.

[José Augusto]

You can try the aggregate function to get the first, the second, ...

=AGGREGATE(14,6,(A2:A11=$B$17)*(B2:B11=$B$18)*INDEX($A$2:$G$11,,MATCH($B$19,$A$1:$G$1,0)),1)

B17 is the company, B18 is the State (column B) and B19 is the State in row 1.