ExcelBanter - Find value with two conditions

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Find value with two conditions (https://www.excelbanter.com/excel-discussion-misc-queries/231570-find-value-two-conditions.html)

Find value with two conditions

Hi,

I am trying to find a value in the follwoing sheet "AO1 1-2009". I want to
find the number 500 and put it in another sheet.

A B C
1 2 3
2 100 50 52
3 101 300 500

I have been trying to do this the following way =SUMPRODUCT((101='AO1
01-2009'!$A$2:$A$45)*(3='AO1 01-2009'!$A$1:$C$1)*('AO1 01-2009'!C2:C45))

However, I guess if you use sumproduct for conditions in different ranges it
does not work.

Please help me out!

Find value with two conditions

One way:

A1 = 101
B1 = 3

=VLOOKUP(A1,'AO1 01-2009'!A1:C10,MATCH(B1,'AO1 01-2009'!A1:C1,0),0)

--
Biff
Microsoft Excel MVP

"Verlaesslichkeit" wrote in
message ...
Hi,

I am trying to find a value in the follwoing sheet "AO1 1-2009". I want to
find the number 500 and put it in another sheet.

A B C
1 2 3
2 100 50 52
3 101 300 500

I have been trying to do this the following way =SUMPRODUCT((101='AO1
01-2009'!$A$2:$A$45)*(3='AO1 01-2009'!$A$1:$C$1)*('AO1 01-2009'!C2:C45))

However, I guess if you use sumproduct for conditions in different ranges
it
does not work.

Please help me out!