Index/Match/If Function HELP!

[ltomlinson]

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

 

[skorpionkz]

all you need here is the IF function in column Q, R and S:

Excel 2010
	C	N	P	Q	R	S
1				Check 1	Check 2	Check 3
2	Name 1	1001	jjj		Name 1
3	Name 2	2400	jjj		Name 2
4	Name 3	8452	mmm			Name 3
5	Name 4	455
6	Name 5	156	nnn	Name 5
7	Name 6	1547	jjj		Name 6
8	Name 7	6548	jjj			Name 7
9	Name 8	2000	mmm		Name 8
10	Name 9	1555
11	Name 10	1001	nnn		Name 10
12	Name 11	2400	jjj		Name 11
13	Name 12	8452	jjj			Name 12
14	Name 13	455	mmm	Name 13
15	Name 14	156
16	Name 15	1547	jjj		Name 15
17	Name 16	6548	jjj			Name 16
18	Name 17	2000
19	Name 18	1555	lll		Name 18
20	Name 19	156
21	Name 20	1547	jjj		Name 20
22	Name 21	6548	mmm			Name 21
23	Name 22	2000	jjj		Name 22
24	Name 23	1555	jjj		Name 23
25	Name 24	156	mmm	Name 24
26	Name 25	1547	lll		Name 25
27	Name 26	6548	nnn			Name 26
Sheet1

Cell Formulas
Range	Formula
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,""),"")

 

[ltomlinson]

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)

 

[bbott]

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.

 

[ltomlinson]

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

 

[bbott]

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

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