Formula for finding an element

  • Thread starter Thread starter bim-bom
  • Start date Start date
B

bim-bom

Hi,

I have a spreadsheet:

1 2 100
5 6 200
5 11 300

I want to find a row which where first column has a specific value and
the second column has a specific value, and print a value in the third
column.

So, I want something like:

= FIND_VALUE (A1:A3 ; 5 ; B1:B3 ; 6 ; C1) // finds 5 in the first column
and 6 in the second
And it should show the value 200
 
One way...

Array entered** :

=INDEX(C1:C3,MATCH(1,IF(A1:A3=5,IF(B1:B3=6,1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
T. Valko pisze:
One way...

Array entered** :

=INDEX(C1:C3,MATCH(1,IF(A1:A3=5,IF(B1:B3=6,1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Click to expand...

Thank you very much - it works perfectly. Now I know how to know make
things like that.
 
If you know that the pairs in columns A and B will always be unique, then you
can use SUMPRODUCT() in pre-2007 Excel, or SUMIFS() in Excel 2007. By
unique, I mean there would not be another row with 5 in a AND 6 in B.

Excel 2003 formula:
=SUMPRODUCT(--(A$1:A$3=5),--(B$1:B$3=6),C$1:C$3)
you can substitute a cell address for 5 and 6.

Excel 2007 formula:
=SUMIFS(C$1:C$3,A$1:A$3,"=" & 5,B$1:B$3,"=" & 6)
again, you can substitute cell addresses for the 5 and 6
 
Back
Top