Trying not to use array formula

[Matto]

K ill try and make this understandable quick, say i Have a table that looks
like this:

Letter Number Value
A 1 33
A 2 43
A 3 18
A 4 27
A 5 95
A 6 21
B 1 19
B 2 67
B 3 88
B 4 56

Now, say i want to return a number from the value column given letter "A"
and Number "6". Can I lookup these two columns successively, and return the
corresponding value number, without using an arry formula, (ie, ctrl, alt,
enter)?

 

[Frank Kabel]

Hi
if column C contains only number and there's only one matching row try:
=SUMPRODUCT(--(A1:A100="A"),--(B1:B100=3),C1:C100)

Note: SUMPRODUCT is internally also an array formula though you don't
have to enter it with CTRL+SHIFT+ENTER

 

[Matto]

Works perfectly, for my knowledge, what does the "--" do

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Aladin Akyurek]

Let A1:C11 house the sample you provided.

In D2 enter & copy down:

=A1&","&B1

Now use:

=INDEX($C$2:$C$11,MATCH(E2&","&F2,$D$2:$D$11,0))

where E2 houses an A and F2 a 6.