Index Match query (I think!)

[krisbuddies]

Morning all


Some help needed please - seems pretty simple but it's beyond my Excel knowledge!!

Data, as follows

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12[/TD]
[TD]3.1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]24[/TD]
[TD]3.2[/TD]
[TD]2.5[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]36[/TD]
[TD]3.3[/TD]
[TD]3[/TD]
[TD]2.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]48[/TD]
[TD]3.4[/TD]
[TD]3.75[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]60[/TD]
[TD]3.5[/TD]
[TD]4[/TD]
[TD]3.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]72[/TD]
[TD]3.6[/TD]
[TD]4.5[/TD]
[TD]3.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]84[/TD]
[TD]3.7[/TD]
[TD]5[/TD]
[TD]3.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]36[/TD]
[TD][/TD]
[TD]3.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A1:D7 are static tables, A9 and C9 are variables.

What I want my formula to do is look at A9 & C9, then add the 2 figures in B1:B7 & D1:D7 together.

So, in this example, the result would be 3.3 + 3 (because I need the formula to see if C9 is equal to or less than the figures in D1:D7.


Does that make sense?? I think this will be some sort of Index Match query, but I'm really struggling to get it to work! Thanks in advance...

 

[Finalfight40]

Is this what you are after?

=INDEX(B1:B7, MATCH(A9, A1:A7, 1)) + INDEX(D1:D7, MATCH(C9, C1:C7, 1))

 

[krisbuddies]

That gives a result of 6.05, but I'm expecting 6.3...


so, the variable in C9 needs be equal to or less than the result in C1:C7. So, in the below example, C9 = 3.5 which is >C3 but <C4, which gives it a result of 3 (in D4)


Does that make sense??

 

[krisbuddies]

sorry, post got cut off!! Ggrrr.... last sentence should read "So, in the below example, C9 = 3.5 which is >C3 but <C4, returning a result of 3 (D4)..."

 

[krisbuddies]

I honestly have no idea why this is happening... weird! Sentence is "So, in the below example, C9 = 3.5 which is greater than C3 but less than C4 = 3 (D4)<c4, so="" returns="" a="" result="" of="" 3="" (d4)<="" font=""></c4,>

 

[Finalfight40]

Of course i would have copied your table out incorrectly, my bad (Wouldnt let me copy and paste it)

 

[steve the fish]

If you use the < symbol you need to put spaces around it or it will cut off the rest of the sentance. You will need something like:

=INDEX(B1:B7,MATCH(1,INDEX(--(A1:A7>=A9),0),0))+INDEX(D1:D7,MATCH(1,INDEX(--(C1:C7>=C9),0),0))

 

[krisbuddies]

Thank you both, really appreciate your help - Steve the fish, that works perfectly

​