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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
Is this what you are after?

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

krisbuddies

New Member
Joined
Jul 30, 2018
Messages
14
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:
 

krisbuddies

New Member
Joined
Jul 30, 2018
Messages
14
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

New Member
Joined
Jul 30, 2018
Messages
14
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

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
Of course i would have copied your table out incorrectly, my bad (Wouldnt let me copy and paste it)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,720
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 30, 2018
Messages
14
Thank you both, really appreciate your help - Steve the fish, that works perfectly
(y)
 

Forum statistics

Threads
1,170,968
Messages
5,873,049
Members
432,957
Latest member
Elelisah

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
Top