Index match help

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
HI,

I'm trying to figure out how to write a formula that will look down through Range(A5:A16) and find the value in cell A2. The next step is to look accross C3:H3 and find the value in cell A3.

Here is the tricky part. The value in A3 needs to be found between the number value in the Range (C3:H3). The fomula is meant to work the sale way as a times table.


C35,000
D3 25,000
E3 100,000
F3 499,999
G3 100,000
H3 5,000,000

Thank you for any help
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
here is an if statement that looks for the correct value

=IF(B20<=C3,1,IF(B20<=D3,2,IF(B20<=E3,3,IF(B20<=F3,4,IF(B20<=G3,5,IF(B20<=H3,6,7))))))

This needs to make up the match for the column search
 

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
Here is the match formula that works.

=INDEX(C5:H16,MATCH(A2,A5:A16,0),MATCH(B2,C3:H3,0))

I need to work the if statement I posted early to be used with the match statement to find the column

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,317
Messages
5,600,912
Members
414,415
Latest member
joshuaba

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