# Index/Match/If Function HELP!

#### ltomlinson

##### New Member
Hello Everyone,

I am having an issue with this function.
I need an Index Function with a table 4 columns wide and 58 rows

Example:
<1000 <2500 >2500
P1: jjj Q1: jack R1: mary S1: Tom
P2: lll Q2: jill R2: frank S2: tammy
P3: MMM Q3: jess R3: beth S3: john
P4: NNN Q4: seth R4: bill S4: Fred

I need the function to look at column C on sheet 1) and if Column P (on sheet 2) (jjj, lll, mmm, nnn) is matches, then i need it to look at the \$ value of Column N
(sheet 1)and if it is <1000 i need it to return the name in Column Q. If it is between 1000<2500 i need it to return the name in column R. and >2500 return the name in column S

I am new to this, so as much help is very much appreciated

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
all you need here is the IF function in column Q, R and S:

Excel 2010
CNPQRS
1Check 1Check 2Check 3
2Name 11001jjj Name 1
3Name 22400jjjName 2
4Name 38452mmmName 3
5Name 4455
6Name 5156nnnName 5
7Name 61547jjjName 6
8Name 76548jjjName 7
9Name 82000mmmName 8
10Name 91555
11Name 101001nnnName 10
12Name 112400jjjName 11
13Name 128452jjjName 12
14Name 13455mmmName 13
15Name 14156
16Name 151547jjjName 15
17Name 166548jjjName 16
18Name 172000
19Name 181555lllName 18
20Name 19156
21Name 201547jjjName 20
22Name 216548mmmName 21
23Name 222000jjjName 22
24Name 231555jjjName 23
25Name 24156mmmName 24
26Name 251547lllName 25
27Name 266548nnnName 26
Sheet1
Cell Formulas
RangeFormula
Q2=IF(OR(\$P2="jjj",\$P2="lll",\$P2="mmm",\$P2="nnn"),IF(\$N2<1000,\$C2,""),"")
R2=IF(OR(\$P2="jjj",\$P2="lll",\$P2="mmm",\$P2="nnn"),IF(AND(\$N2>1000,\$N2<2500),\$C2,""),"")
S2=IF(OR(\$P2="jjj",\$P2="lll",\$P2="mmm",\$P2="nnn"),IF(\$N2>2500,\$C2,""),"")

I need This to return the name from The table Column P (Sheet2) based on the name in column C (Sheet1) and the dollar amount criteria in Column N (Sheet1)

Assuming the first name and dollar amount on sheet 1 are in C1 and N1, try:

=INDEX(Sheet2!\$Q\$1:\$S\$58,MATCH(C1,Sheet2!\$P\$1:\$P\$58,0),MATCH(N1,{0,1000,2500,100000},1))

Copy down.

Last edited:
You are AWESOME!!!!!
That Worked perfectly!!!!

You are AWESOME!!!!!
That Worked perfectly!!!!

Thanks for the feedback. I'm glad it worked out for you.

Replies
1
Views
217
Replies
3
Views
316
Replies
4
Views
352
Replies
4
Views
185
Replies
4
Views
431

1,221,218
Messages
6,158,595
Members
451,501
Latest member
andysacko

### 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?

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