IF Formula help please

[yvcamasia407]

Hello everyone. Here's hoping I can get some help to write an IF formula. I've been trying without success. Here are my data:

Table 1 - Employee Status
EX
NEX
PT
OC

Table 2 - Accrual Rate
Tier 1 Tier 2 Tier 3 Tier 4 Tier 5 Tier 6
<10 <15 <16 <17 <20 <25
EX 3.08 4.62


NEX 1.54 3.08 4.62 4.92 5.23 5.54 5.85 6.15
PT
OC

I need a formula that says, if a Status is EX, lookup in Table 2, if < than 10=3.08, <15=4.62...., IF NEX, PT or OC, lookup in table 2, if <5=1.54, if<10=3.08.

Thank you,

Becky

 

[Luke M]

Hi Becky,
Looks like your table got a little distorted. Would you mind uploading a sample workbook with your tables laid out (and your goal)? It might help us understand better what you are trying to do.

 

[yvcamasia407]

Thank you. Here it is...

 

[Debraj]

Hi Becky..

Please check the attached.. few changes has been made..
Your task is to check.. what I have changes from the original data..

then you can simply apply formula as.. below..

In I4.. use formula as
=HLOOKUP(I4,IF(E4="EX",$E$17:$L$18,$E$21:$L$24),IF(E4="ex",2,MATCH(E4,$D$21:$D$24,0)))

BTW.. Can you please also check your below statement..

the status of this employee is NEX, with 12 years experience, accrual rate is 3.08

I think.. it should be 4.62..

 

[Luke M]

Becky,

See if the attached will work. I had to modify your table a little bit, but hopefully you can see the idea behind the formula.

 

[yvcamasia407]

HLookup, I did not even thought about it. Thank you.
Here's the thing, when I changed the numbers below the Tiers, I get #N/A. I wonder why. Because the tier will change according to entity, just like the number for the years of service.

Otherwise, yes this will work.

Thank you.

 

[Debraj]

ha ha ha... Becky..
Luke already gives you the file.. check our Teams "same thinking power" and your answer in luke's attachment..

<--Read this line as "at least this many years…"
Each number "triggers" the formula to read that column

just try to adapt it..

 

[yvcamasia407]

Thank you Luke,,,,,,I played with it and now the formula is working. Thank you very much.
I truly appreciate your time and sharing your expertise.

Just so you know, this formula also worked:
=IFERROR(IF(I3="E",IF(H3<=0.999999,80/26,IF(H3<9.99999,120/26,IF(H3<10.9999,128/26,IF(H3<11.9999,136/26,IF(H3<12.9999,144/26,IF(H3<13.9999,152/26,160/26)))))),IF(H3<=0.999999,SUM(R3:U3)*0.0192,IF(H3<4.9999,SUM(R3:U3)*0.0385,IF(H3<8.9999,SUM(R3:U3)*0.0577,IF(H3<10.9999,SUM(R3:U3)*0.0615,IF(H3<11.9999,SUM(R3:U3)*0.0654,IF(H3<12.9999,SUM(R3:U3)*0.0692,IF(H3<13.9999,SUM(R3:U3)*0.0731,SUM(R3:U3)*0.0769)))))))),0)

But I am using your formula.

Debraj--thank you for your input. greatly appreciate your time.

 

[yvcamasia407]

Deb--you are right, Luke already gave me the formula==I just need to put my thinking hat on.
Thank you.

Regards!