multiple criteria computation!!

Untitled.png



need an expert on this please!


am trying to compute staff's contribution in F2 based on criteria B2, C2, D2 AND E2.

1) if B2 = "Y", F2 = "SPR"
2) if D2 = "N", F2 = 0 then if all FALSE,
3) search C2 within B8:E8 of CON'EE table A7:E12 and search E2 in A9:A12, both just need the closest match and return intersecting value

example #1:
Staff A, Age 39 and Gross Pay $650, F2 will return value (B10*(E2-500)) and TRUNC -1

example #2
Staff B, Age 68 and Gross Pay $6800, F2 will return (E12)


*if return value falls under Row 9 or Row 12, = (intersection cell value)
*if return value falls under Row 10, = (intersection cell value*(Gross-500)) and TRUNC -1
*if return value falls under Row 11, = (intersection cell value*Gross) and TRUNC -1


hope the above is understood to you all. really having a hard time trying to get this right!! really appreciate any help now.

thank you!!!

Hello and welcome to the forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi there! thank you for the reply!!

ok, i re-read your reply. had a misunderstanding there. haha hang on

Hi there. as attached

Why do some rows (such as F4, F9, and F10) multiply the CON'EE table value by column E when others (such as F7) don't?

Here's where I'm at so far. I edited your table to show the minimum values in each group.

F2 =IF(B2&C2="YN",0,IF(B2&C2="YY","SPR",IF(B2&C2="NY",TRUNC(INDEX(B$17:E$20,MATCH(E2,A$17:A$20,1),MATCH(D2,B$16:E$16,1))*(E2-IF(AND(E2>500,E2<=750),500,0)),-1))))

This gives the expected results for all of the rows except for F7 due to the question that I asked in post #5.

See attachment for clarification.

I understand now, the only multipliers are in rows 17-19.

Try this in F2:
=IF(B2&C2="YN",0,IF(B2&C2="YY","SPR",IF(B2&C2="NY",IF(E2>=6000,LOOKUP(D2,B$16:E$16,B$20:E$20),TRUNC(INDEX(B$17:E$20,MATCH(E2,A$17:A$20,1),MATCH(D2,B$16:E$16,1))*(E2*IF(E2>=6000,0,1)-IF(AND(E2>500,E2<=750),500,0)),-1)))))

OMG YOU ARE A LIFE SAVER!!! thank you so much. its working perfectly!!! im so grateful for this!

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.