A long formula question

[maxw7]

Hello,

I have a situation that I'm looking for help on a formula that seems to be stumping me. The data that I'm working with are members accounts but what I'm specifically needing to pull is members money market accounts which would be referenced as Item #'s 7, 8, and 10. The two columns of data that I'm working with are of course Item # and Balance. Now the way we have a program set up is we are breaking these members money market accounts into tiers based on their balance. Those tiers are as follows:

Tier 1.1 - Less than 2,500
Tier 1.2 - 2,501 - 25,000
Tier 1.3 - 25,001 - 50,000
Tier 1.4 - 50,001 - 100,000
Tier 1.5 - Greater than 100,000

So this seems pretty easy up front but here is where the problem occurs. For some reason the data that is being pulled over from our system is classifying these money market accounts as item #'s 7, 8, and 10. The program is only able to look at item #'s to which it will then put the corresponding item # in it's line item (tier breakdowns) of our financials. So what I need to find a formula for is a way to look for the item #'s above and when the formula finds the item # to then look at the balance column. Once it looks at the balance column I need it to return the tier number that the balance would fall into. For example if it finds a item # 10 and the balance of that item is $5,000 I need the cell to populate "1.2".

Now I know that a pivot table could probably be used here but the file we are using is imported into a software that cannot look at tables. It can only look at one column of data. So that is why I need to be able to have tier numbers returned which I will then replace the Item # with the tier number. I have done something similar with IF AND formula's but I didn't have the multiple tiers like I have here.

Thanks in advance!

 

[StephenCrump]

Maybe I'm missing something, but don'tyou just need a simple lookup:

B2: =VLOOKUP(A2,MyLookupTable,2,1)

Book1
	A	B	C	D	E
1	Balance	Tier		MyLookupTable
2	$5,000	1.2		-$999,999	1.1
3	$1,000	1.1		$2,500	1.2
4	$200,000	1.5		$25,000	1.3
5	$60,000	1.4		$50,000	1.4
6	$26,000	1.3		$100,000	1.5
Sheet1

You may need to adjust the boundaries, e.g. maybe $2,500 is Tier 1 and $2,500.01 is Tier 2?

 

[maxw7]

Maybe I'm missing something, but don'tyou just need a simple lookup:

B2: =VLOOKUP(A2,MyLookupTable,2,1)

	A	B	C	D	E
1	Balance	Tier		MyLookupTable
2	$5,000	1.2		-$999,999	1.1
3	$1,000	1.1		$2,500	1.2
4	$200,000	1.5		$25,000	1.3
5	$60,000	1.4		$50,000	1.4
6	$26,000	1.3		$100,000	1.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

You may need to adjust the boundaries, e.g. maybe $2,500 is Tier 1 and $2,500.01 is Tier 2?

Hey Stephen, Thanks for the response. I was sort of hoping that I would be able to stay away from any sort of table needing to be used.

 

[maxw7]

Here is the formula that I'm currently using. Though it is working I'm hoping that there is something a bit cleaner that would be a better option.

=IF(AND(B2=7,C2<2500),"7.1",IF(AND(B2=7,C2>2500,C2<25000),"7.2",IF(AND(B2=7,C2>25000,C2<50000),"7.3",IF(AND(B2=7,C2>50000,C2<100000),"7.4",IF(AND(B2=7,C2>100000),"7.5",IF(AND(B2=8,C2<2500),"7.1",IF(AND(B2=8,C2>2500,C2<25000),"7.2",IF(AND(B2=8,C2>25000,C2<50000),"7.3",IF(AND(B2=8,C2>50000,C2<100000),"7.4",IF(AND(B2=8,C2>100000),"7.5",IF(AND(B2=10,C2<2500),"7.1",IF(AND(B2=10,C2>2500,C2<25000),"7.2",IF(AND(B2=10,C2>25000,C2<50000),"7.3",IF(AND(B2=10,C2>50000,C2<100000),"7.4",IF(AND(B2=10,C2>100000),"7.5",B2)))))))))))))))

 

[Eric W]

Try:

=IF(OR(B2={7,8,10}),LOOKUP(C2,{-999999,2500,25000,5000,100000},{1.1,1.2,1.3,1.4,1.5}),"Non-Money Market")

 

[maxw7]

Try:

=IF(OR(B2={7,8,10}),LOOKUP(C2,{-999999,2500,25000,5000,100000},{1.1,1.2,1.3,1.4,1.5}),"Non-Money Market")

This worked perfectly! Thank you so much for making this easier. Nice to see it come from a fellow Utahn as well (Farmington).

 

[Eric W]

I drive by there every day, I'll wave next time I go by!

Also, I noticed a typo in my formula, it should be:

=IF(OR(B2={7,8,10}),LOOKUP(C2,{-999999,2500,25000,50000,100000},{1.1,1.2,1.3,1.4,1.5}),"Non-Money Market")

I left off a zero. Make sure you have that right.

Anyway, glad I could help!