Comparing List and returning value

[fireguard]

Greetings all,
I would like to get some advice on the following
First I Input two values at cells B2(time) and B3(wavelength)
The two values would than be matched against two columns to return a third and recorded in cell L2.
In the example below, B2=1, B3=3. The values in column I and J would be compared so that the value recorded in cell L2 would equal 30.
cell B2 = 1
cell B3 = 3

I J K
ג time Value
1 1 30000000000
1 2 30000000000
1 3 30
1 4 40
1 5 50
1 6 60
1 7 70
1 8 40
1 9 60


I have tried several options but seem to fall short.
Your assistance would be greatly appreciated.

 

[Aladin Akyurek]

Are you trying to say that I must equal B2 and J must equal B3?

 

[fireguard]

Are you trying to say that I must equal B2 and J must equal B3?

Hi
Thanks for the reply
In the bigger scheme, B2 allows the person to enter any between 1-12. At present I have limited to 1

B3 allows the person to enter a number between 1-9.
column I would match the number 1 (in this example they all equal 1)against column J, so that the combination of 1 in column I and 3 in column J would return a value of 30 from column K. This number from column K would then be displayed in cell L2.

Hope this helps

I appreciate any assistance you could offer.

 

[PCL]

Assuming your data from Row 2 to 10
L2=sumproduct((i2:i10=b2)*(j2:j10=b3)*(k2:k10))

 

[Aladin Akyurek]

Is the answer is yes? I think it is.

Control+shift+enter, not just enter.

=INDEX($K$2:$K$100,MATCH(B2,IF($J$2:$J$100=B3,$I$2:$I$100),0))

 

[fireguard]

Assuming your data from Row 2 to 10
L2=sumproduct((i2:i10=b2)*(j2:j10=b3)*(k2:k10))

Wow! Thank you so much. this works a treat....

 

[Aladin Akyurek]

But also it also sums multiple occurrences...

 

[fireguard]

But also it also sums multiple occurrences...

It has worked extremely well and I have been able to expand it to include the whole project I am working on. You have saved me...........hours of work. cheers

 

[Aladin Akyurek]

It has worked extremely well and I have been able to expand it to include the whole project I am working on. You have saved me...........hours of work. cheers

SUMPRODUCT sums, INDEX/MATCH retrieves. These are different operations.