Index Match query (I think!)

krisbuddies

New Member
Joined
Jul 30, 2018
Messages
14
Morning all


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

Data, as follows

ABCDE
1123.122
2243.22.52.5
3363.332.75
4483.43.753
5603.543.25
6723.64.53.5
7843.753.75
8
9363.5

<tbody>
</tbody>

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...
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Is this what you are after?

=INDEX(B1:B7, MATCH(A9, A1:A7, 1)) + INDEX(D1:D7, MATCH(C9, C1:C7, 1))
 
Upvote 0
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?? :confused:
 
Upvote 0
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)..."
 
Upvote 0
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,>
 
Upvote 0
Of course i would have copied your table out incorrectly, my bad (Wouldnt let me copy and paste it)
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top