# Index Match query (I think!)

#### krisbuddies

##### New Member
Morning all

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

Data, as follows

 A B C D E 1 12 3.1 2 2 2 24 3.2 2.5 2.5 3 36 3.3 3 2.75 4 48 3.4 3.75 3 5 60 3.5 4 3.25 6 72 3.6 4.5 3.5 7 84 3.7 5 3.75 8 9 36 3.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

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

#### Finalfight40

##### Active Member
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
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

##### New Member
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
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
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
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
Thank you both, really appreciate your help - Steve the fish, that works perfectly

Replies
6
Views
176
Replies
1
Views
106
Replies
6
Views
184
Replies
1
Views
133
Replies
4
Views
127

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.

### Which adblocker are you using?

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

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